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

Delete files from a folder using File System Task in SQL Server Using SSIS

We know all the various methods to delete the particular or all files from the specified folder using some of the methods like xp_delete_file, Ole Automation Procedures and with xp_cmdshell command line utility which we used for the old files archive or cleanup purpose. Here I am going to share some of the screens which delete the files with SSIS using File System Task. Let me share them one by one.

1. Folder having some test files,

2. Drag and drop Foreach Loop Container and File System Task. Foreach Loop Container used to get all the files inside that folder one by one and process with File System Task. Open the Foreach Loop Container properties, GOTO Collection tab and select Foreach File Enumerator as specified in the screen below.

3. Select the folder from where need to delete the files. Also apply extension if you need.

4. For the file assignment, we need one variable and the values allocated from the Foreach Loop Container process. So assign it from variable mapping as per shot taken below.