SSIS- script task to export data from database table to .csv file

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();

        }

    }

}