How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package - SSIS Tutorial

Scenario: Download Script

You are working as ETL developer / SSIS Developer. You need to developer an SSIS Package that should read all CSV files from a folder and create a new Excel file with Date time and load CSV files to it. Each CSV file should be loaded to new excel sheet.

CSV file can have same column structure or different. 

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 datetime should of log file should match with excel file it was trying to create.



Load CSV files to Excel File Dynamically in SSIS Package by using Script Task - SSIS tutorial


Solution:

We will be using Script Task to read all the csv files from a folder and then load to newly created Excel File.As number of files can be less or more, we need to use loop in Script Task to get each file and load dynamically.
We are going to create variables 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 File
FileDelimiter : Provide the delimiter such as comma (,), Pipe( | ) Whatever your files are using.
FileExtension : Provide the extension of files that you would like to read.
SourceFolderPath : Source folder path where text files exists
ExcelFileName : Provide the Name of Excel File you would like to create

Create variables in SSIS Package to load all the text files to an Excel File by using Script Task

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.
Use SSIS Packagre variables in Script Task to write all text files to Excel File dynamically in SSIS Package


Step 3: Add Script to Script task Editor in SSIS Package to Load Each csv File to Excel Sheet in Excel File
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.
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //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 ExcelFileName = Dts.Variables["User::ExcelFileName"].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)
                {
                    
                    //MessageBox.Show(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)";
                    file.Close();
                    //MessageBox.Show(CreateTableStatement.ToString());


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



                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;

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

                    //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, "],[") + "]";
                            
                        }
                        else
                        {
                            string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "')";
                           // MessageBox.Show(query.ToString());
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

                        }
                        counter++;
                    }
                    Excel_OLE_Con.Close();
                    SourceFile.Close();
                    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+".log"))
         
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;

                }
                               
            }


Step 4: Save Script and Execute SSIS Package to Load Each CSV From Folder to an Excel File 
Save the script in Script task editor and close the window. Run your SSIS Package, It should read each text file, create new excel sheet in excel file and load it.

I ran my SSIS Package for above sample files and here is my Excel File created by SSIS Package.
How to load all CSV files from Folder to an Excel File Dynamically in SSIS Package - SSIS Tutorial