SSIS -How to Convert Excel File To CSV ( Comma Separated values) File in SSIS Package

Scenario:

Our business users create excel file. We need to send this file to vendor but vendor only accepts csv ( Comma separated values) file. We need to convert excel file into csv file by using SSIS Package. 

Solution:

In this post we will be using Script Task to convert our Excel file into Comma separated values file. The SSIS Package can be modified by using For- each loop container in case where you want to read multiple excel files and convert all of them to csv files. 

Step 1: 

Create an excel file if you don't have one. I have created one for this post as shown below

Fig 1: Sample Excel for to convert to csv by SSIS Package

Step 2: 

Create two variables, one that is pointing to Source Excel file and one for destination csv file as shown below.

Fig 2: Create Source and Destination variables for Excel to Csv conversion SSIS Package

Step 3: 

Bring the Script Task to the Control Flow Pane and then choose the above variables in ReadOnlyVariables list.

Fig 3: Map Source and Destination Path variables in Script Task

Click on Edit Script and paste the below script.

#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::Var_ExcelFilePath"].Value.ToString() +

                ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query = "SELECT * FROM [Sheet1$]";
            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::Var_CsvFilePath"].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

    }
}

The code I added is in bold, You can copy this code to your script task and leave rest of your code as it is.

Let's run the Package and check if the file is created successfully.

Fig 4: Converted csv File from Excel by using SSIS Package