SSIS- script task in C# to export data from database table to .xls format with each data separated by column in tab delimited form
Recently I faced a challenge while writing a script task in SSIS (C#) exporting data from database table to ".xls" format where in each data of a row should be under different column in EXCEL.
While I tried with the solution to export data from database table to ".csv" form using my earlier post as given in the link below, but I got stuck after the data got exported. I found the whole data of a row under one column separated by (","). The link below gives you C# code to export data from database table ".csv" format comma separated.
http://neil037.blogspot.in/2013/07/ssis-script-task-to-export-data-from.html
So after lot of analysis I found out the solution which I implemented below. The solution uses
Microsoft.Office.Interop.Excel to render the data in separate column.
The code written by me is as below. Please let me know if anyone has any doubts on the same.
/*
Microsoft SQL Server Integration Services
Script Task
Write scripts using Microsoft Visual C#
2008.
The ScriptMain is the entry point class of
the script.
*/
using System;
using
System.Data;
using
Microsoft.SqlServer.Dts.Runtime;
using
System.Windows.Forms;
using
System.Web;
using
System.Collections.Generic;
using v=
Microsoft.Office.Interop.Excel;
//using
System.Linq;
//using
System.Web.Security;
//using
System.Web.UI;
//using
System.Web.UI.WebControls;
//using
System.Web.UI.Adapters;
//using
System.Web.UI.WebControls.WebParts;
//using
System.Web.UI.HtmlControls;
using
System.Data.SqlClient;
using
System.Text;
using System.IO;
namespace
ST_f9e64226a54d4da28a70f42ab090a637.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain :
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
string
_FileName = "";
// string
unicode = "utf-8";
#region VSTA generated code
enum ScriptResults
{
Success =
Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure =
Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this
method when the task executes.
To access the object model, use the
Dts property. Connections, variables, events,
and logging features are available
as members of the Dts property as shown in the following examples.
To reference a variable, call
Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call
Dts.Log("This is my log text", 999, null);
To fire an event, call
Dts.Events.FireInformation(99, "test", "hit the help
message", "", 0, true);
To use the connections collection
use something like the following:
ConnectionManager cm =
Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data
Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated
Security=SSPI;Auto Translate=False;";
Before returning from this method,
set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
// TODO:
Add your code here
string
path = Dts.Variables["User::Input_File_Folder"].Value.ToString();
String
fname = Dts.Variables["User::Output_File_Folder"].Value.ToString()
+ "\\" + GetFileNames(path);
Dts.TaskResult = (int)ScriptResults.Success;
//GetFileNames(path);
CreateCSVFile(GetTableData(),
fname);
}
//public
static Encoding GetEncoding(string unicode);
public string GetFileNames(string
path)
{
string
tFileName = new DirectoryInfo(path).GetFiles("*.xls")[0].Name;
//string[]
files = Directory.GetFiles(path);
//string
tfileName=files[0].ToString(); //as assuming always one file
string[]
fileparts = tFileName.Split('.');
_FileName = tFileName;
return
_FileName;
}
public
System.Data.DataTable GetTableData()
{
String
connstring = Dts.Variables["User::outputdatabaseConnectionString"].Value.ToString();
SqlConnection
conn = new SqlConnection(connstring);
SqlCommand
cmd = new SqlCommand("SELECT * from TABLE_NAME",conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter
adap = new SqlDataAdapter(cmd);
System.Data.DataTable dt = new System.Data.DataTable();
adap.Fill(dt);
return dt;
}
public void CreateCSVFile(System.Data.DataTable dt, string
strFilePath)
{
v.Application
tExcel = new v.Application();
tExcel.Visible = true;
//v.Workbook
wk = (v.Workbook)(tExcel.Workbooks.Add(System.Reflection.Missing.Value));
//v.Worksheet
sheet = (v.Worksheet)wk.ActiveSheet;
v.Workbook
wk = tExcel.Workbooks.Add(v.XlWBATemplate.xlWBATWorksheet);
v.Worksheet
sheet = (v.Worksheet)wk.Worksheets[1];
sheet.Name = "sans nom";
if
(sheet == null)
{
Console.WriteLine("Worksheet could not be created. Check that your
office installation and project references are correct.");
}
int
iColCount = dt.Columns.Count;
for
(int i = 0; i < iColCount; i++)
{
((v.Range)sheet.Cells[1,i+1]).Value2
=dt.Columns[i].ToString();
}
// Now
write all the rows.
int
j = 0;
foreach
(DataRow dr in
dt.Rows)
{
for
(int i = 0; i < iColCount; i++)
{
((v.Range)sheet.Cells[j+1,i+1]).Value2 = dr[i].ToString();
}
j++;
}
wk.SaveAs(strFilePath,
v.XlFileFormat.xlXMLSpreadsheet,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
v.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing);
wk.Close(true,Type.Missing,Type.Missing);
}
}
}
