SSIS- script task to export data from database table to .csv file
User::INPUT_FILE_PATH - input file path to get output file name same as the input file name
User::OutputPath - output path where we get the output in .csv format
User::outputdatabaseConnectionString - Database connection string
/*
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
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
public void Main()
{
// TODO:
Add your code here
string
path = Dts.Variables["User::INPUT_FILE_PATH"].Value.ToString();
String
fname = Dts.Variables["User::OutputPath"].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("*.csv")[0].Name;
//string[]
files = Directory.GetFiles(path);
//string
tfileName=files[0].ToString(); //as assuming always one file
string[]
fileparts = tFileName.Split('_');
string
filePart = fileparts[0] + "_" +
fileparts[1] + "_" + fileparts[2]
+ "_" + "Othertext"
+ "_" + fileparts[5];
_FileName = filePart;
return
_FileName;
}
public DataTable GetTableData()
{
String
connstring = Dts.Variables["User::outputdatabaseConnectionString"].Value.ToString();
SqlConnection
conn = new SqlConnection(connstring);
SqlCommand
cmd = new SqlCommand("select * from table", conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter
adap = new SqlDataAdapter(cmd);
DataTable
dt = new DataTable();
adap.Fill(dt);
return
dt;
}
public void CreateCSVFile(DataTable
dt, string strFilePath)
{
StreamWriter
sw = new StreamWriter(strFilePath,
false);
int
iColCount = dt.Columns.Count;
for
(int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if
(i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
// Now
write all the rows.
foreach
(DataRow dr in
dt.Rows)
{
for
(int i = 0; i < iColCount; i++)
{
if
(!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if
(i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}
}
}
