How to Convert Excel File To CSV File by using Script Task in SSIS Package

Business Case: The reason why we need to convert into CSV Format from Excel file is because of when i am Loading data from Excel file in SQL Server Table one of column Data  in Excel is Combination of Sting and Numeric Mixed Data types.So when we are Loading This Data into Particular Column in SQL Server Table , Excel Source Identifying First 8 Rows data Data type and all remaining rows  it is Loading as Nulls.

To Avoid This kind null value Loading for Numeric values we need to convert Excel file to CSV to load all values with out having any issue about Datatype. Business User are not providing CSV files.
We need to convert This Excel to csv in our package level only.

Solution:

Step-1 : Create two Variable Having path of Excel and Destination Path of CSV file.

Step-2   take Script Task  Add Those two variables as Read only Variables.

Step-3 Click on Edit Script and paste the below script.

Please change Sheet Name in below code as per your Excel Sheet Name.My case it is

 SELECT * FROM [Emp$]

Note:- If your file is .xls then use below code

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
#endregion

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    ///

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    ///
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // TODO: Add your code here
            //File DataTable from Execel Source File. I have used Sheet1 in my case, if your sheet name is different then change it.
            string ConnString;
            ConnString = "Provider=Microsoft.Jet.OLEDB.4.0.0;Data Source=" + Dts.Variables["User::ExcelFileSource"].Value.ToString() +
                           ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query = "SELECT * FROM [Emp$]";
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);


            //Create csv File

            using (var sw = new StreamWriter(Dts.Variables["User::CSVFileSource"].Value.ToString()))
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += "\"" + datatable.Rows[row][col].ToString() + "\",";

                    }
                    //remove last , from row
                    strRow = strRow.Remove(strRow.Length - 1);
                    //write row to file
                    sw.WriteLine(strRow);
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}



Note:- If your file is .xlsx then use below code

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
#endregion

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    ///

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    ///
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // TODO: Add your code here
            //File DataTable from Execel Source File. I have used Sheet1 in my case, if your sheet name is different then change it.
            string ConnString;
            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::ExcelFileSource"].Value.ToString() +
                           ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query = "SELECT * FROM [Emp$]";
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);


            //Create csv File

            using (var sw = new StreamWriter(Dts.Variables["User::CSVFileSource"].Value.ToString()))
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += "\"" + datatable.Rows[row][col].ToString() + "\",";

                    }
                    //remove last , from row
                    strRow = strRow.Remove(strRow.Length - 1);
                    //write row to file
                    sw.WriteLine(strRow);
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}