How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package - SSIS Tutorial

Scenario : Download Scripts

You are working as ETL developer / SSIS Developer with Insurance company. You get bunch of text files in a folder on daily basis. You want to create an SSIS Package that should read all the text files/csv files and load to single excel sheet after create an Excel file with Datetime. As the data will be loaded from multiple text files to Single Excel Sheet, we would like to add a column called "CSVFileName". By having that information we can tell the record came from which text file.



As we need to load all the csv files to single excel sheet. The definition of all text files should be same. I have created another post How to load all csv files as separate excel sheets to Excel file if you would like to take a look.


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.


How to Load all csv files to single excel sheet to Excel File in SSIS Package by using Script Task


Solution:

We will be using Script Task to read all the csv files or text files from a folder and then load to newly created Excel File to single Sheet. .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, I am also using the same value for Sheet Name.

Create variables in SSIS Package to load all the text files to an Excel Sheet in 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 all csv files to single Excel Sheet
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 = "";
                Int16 FileCnt = 0;
                
                //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 [" + ExcelFileName + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text,[CSVFileName] 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;

                    //Create Sheet if does not exists
                    if (FileCnt == 0)
                    {                        
                        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, "],[") + "],[CSVFileName]";
                            
                        }
                        else
                        {
                            string query = "Insert into [" + ExcelFileName + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "','"+filenameonly+FileExtension+"')";
                           // MessageBox.Show(query.ToString());
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

                        }
                        counter++;
                    }
                    Excel_OLE_Con.Close();
                    SourceFile.Close();
                    FileCnt += 1;
                    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 the Script and Run SSIS Package to Load all CSV files to an Excel Sheet 
Save the script and then close script task editor windows. Run your SSIS Package, it should read all the text files as per variable values and load them to excel sheet after creating new excel file with datetime.

I execute my SSIS Package, It created single Excel file with Single Excel Sheet and load data from all CSV files. 
How to Load all csv/text files to Excel Sheet in Excel file by using SSIS Package- Script Task C#









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

1 comment:

  1. You are my inspiration I possess few web logs and sometimes run out from to post . spreadsheet expert

    ReplyDelete