How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task - SSIS Tutorial

Scenario: Download Script

You are working as ETL Developer / SSIS Developer for Car Insurance company. They receive text files in their source folder. You need to create an SSIS Package that should convert these text files into Excel Files. The Excel File Name should be the same as Text file and Sheet Name should also be same as file name.

Here are couple of samples files I am using for test

Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created.The name of log file will be same like your source file that Package was processing.


We care going to use Script Task in SSIS Package to convert Text/CSV/Tab delimited files to Excel Files. We are going to create variable in our SSIS Package such as FileExtension and FileDilimeter. By changing the values of these variable we can use our SSIS Package to handle different type of text files.

Step 1: Create new SSIS Package with Variables to Make it Dynamic 
Open SSDT ( Sql Server Data Tools) and create new SSIS Package. After that create below variables

DestinationFolderPath: Folder path where you would like to create your Excel Files
FileDelimiter : Provide the delimiter such as comma (,), Pipe( | ) Whatever your files are using.
FileExtension : Provide the extension of files you would like to convert such .txt, .csv
SourceFolderPath : Source folder path where text files exists

Create Variables in SSIS Package to use in Script Task to Convert Text Files to Excel Files

Step 2: Add Script Task to SSIS Package and Map Variables
Bring Script Task to Control Flow Pane and open it by double clicking. Add the SSIS Package variables to it so we can use inside.
Add variables to Script Task in SSIS Package to use for converting CSV files to Excel Files

Step 3: Add Script to Script task Editor in SSIS Package to Convert Text Files to Excel Files 
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;
using System.Data.OleDb;

Under public void Main() { 
I have added below code. 


                //Declare Variables
                string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
                string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
                string FileExtension= Dts.Variables["User::FileExtension"].Value.ToString();
                string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
                string CreateTableStatement = "";
                string ColumnList = "";
                //Reading file names one by one
                string SourceDirectory = SourceFolderPath;
                string[] fileEntries = Directory.GetFiles(SourceDirectory,"*"+FileExtension);
                foreach (string fileName in fileEntries)
                    // do something with fileName
                    //Read first line(Header) and prepare Create Statement for Excel Sheet
                    System.IO.StreamReader file = new System.IO.StreamReader(fileName);
                    string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
                    CreateTableStatement = (" Create Table [" + filenameonly + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";

                    //Construct ConnectionString for Excel
                    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly
                        + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
                    OleDbConnection Excel_OLE_Con = new OleDbConnection();
                    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                    //drop Excel file if exists
                    File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xlsx");
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;

                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Cmd.CommandText = CreateTableStatement;

                    //Writing Data of File to Excel Sheet in Excel File
                    int counter = 0;
                    string line;

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                        if (counter == 0)
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
                            string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "')";
                           // MessageBox.Show(query.ToString());
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;

                    Dts.TaskResult = (int)ScriptResults.Success;
            catch (Exception exception)

                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString() 
                    + "\\" +"ErrorLog_"+DateTime.Now.ToString("yyyyMMddHHmmss")+".log"))
                    Dts.TaskResult = (int)ScriptResults.Failure;


Step 4: Save Script and Run your SSIS Package to Convert Text Files to Excel Files 
Save the script in Script Task Editor and execute your SSIS Package. It should read each of the file from Source Folder and create new excel file in Destination Folder. The package do not delete the files from Source Folder. You can add that script to Script Task or use File system task to do that.

As I had two text files, It created two excel files for me. One for each Text file.

How to convert CSV files to Excel Files in SSIS Package by using Script Task- C# scripting language

Check out our other posts/videos for Dynamic Excel Source and Destination
  1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
  2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
  3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
  4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
  5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
  6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
  7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
  8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
  9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
  10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
  11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
  12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
  13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
  14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
  15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
  16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
  17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
  18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
  19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
  20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
  21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
  22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
  23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
  24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package