How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package - SSIS Tutorial

Scenario: Script to Download

You are working as ETL Developer / SSIS developer. You got this requirement where you get single or multiple Excel files in Source or Input folder. Each File Can have single Sheet or Multiple Sheets. 
You need to create CSV file for each of the excel sheet in Destination Folder. The csv file name should be ExcelFileName_SheetName.csv.

Here are sample files with multiple Sheet that I am going to use for this post.

How to create CSV File for each Excel Sheet in SSIS Package by using Script Task

Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.
Create CSV files dynamically from Excel Files and Sheets - Script Task C#


Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.
How to create csv file for each of the Excel Sheet from Multiple Excel Files in SSIS Package

Solution:

We are going to use Script Task with C# as scripting language in our SSIS Package to preform this requirement. 

Step 1:
Create three variables as shown below.
SourceFolderPath: This is the folder where your Excel files should exists. 
DestinationFolderPath: This variable should hold the folder path where you want to create csv files
FileDelimited: You can provide comma(,) or Pipe( | ) or any other character you like to use as delimiter in your csv file. This will give us opportunity to change to any delimiter without changing the package. I have used comma in this post.

How to use variable in Script Task to Create CSV File for Excel Sheets Dynamically in SSIS


Step 2:
Get the Script Task from Toolbox and drag to Control Flow Pane. Open Script Task by double click and then map the variables as shown. 

How to map variables in Script Task for Creating csv file from Excel Sheet dynamically in SSIS


Step 3:
Click on Edit Script Button in above snapshot and it will open Script Task Editor. Under 
#region Namespaces
add
using System.IO;
using System.Data.Oledb;

Then go to public void Main()
{
and paste below code
string SourceFolderPath=Dts.Variables["User::SourceFolderPath"].Value.ToString();
            string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
            string FileDelimited = Dts.Variables["User::FileDelimited"].Value.ToString();
            var directory = new DirectoryInfo(SourceFolderPath);
            FileInfo[] files = directory.GetFiles();
            
            //Declare and initilize variables
            string fileFullPath = "";
            
            //Get one Book(Excel file at a time)
            foreach (FileInfo file in files)
            {
                    string filename = "";
                    fileFullPath = SourceFolderPath+"\\"+file.Name;
                    filename = file.Name.Replace(".xlsx","");
                    MessageBox.Show(fileFullPath);
               
                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR="YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);

                
                //Get Sheet Name
                   cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname;
                sheetname="";
           foreach (DataRow drSheet in dtSheet.Rows)
            {
                if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                {
                     sheetname=drSheet["TABLE_NAME"].ToString();
                     //Display Sheet Name , you can comment it out
                    // MessageBox.Show(sheetname);

                     //Load the DataTable with Sheet Data
                     OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + "]", cnn);
                     //cnn.Open();
                     OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                     DataTable dt = new DataTable();
                     adp.Fill(dt);

                    //drop $from sheet name
                     sheetname = sheetname.Replace("$", "");

                    //Create CSV File and load data to it from Sheet
                     StreamWriter sw = new StreamWriter(DestinationFolderPath+"\\"+filename+"_"+sheetname+".csv", false);
                     int ColumnCount = dt.Columns.Count;

                     // Write the Header Row to File
                     for (int i = 0; i < ColumnCount; i++)
                     {
                         sw.Write(dt.Columns[i]);
                         if (i < ColumnCount - 1)
                         {
                             sw.Write(FileDelimited);
                         }
                     }
                     sw.Write(sw.NewLine);

                     // Write All Rows to the File
                     foreach (DataRow dr in dt.Rows)
                     {
                         for (int i = 0; i < ColumnCount; i++)
                         {
                             if (!Convert.IsDBNull(dr[i]))
                             {
                                 sw.Write(dr[i].ToString());
                             }
                             if (i < ColumnCount - 1)
                             {
                                 sw.Write(FileDelimited);
                             }
                         }
                         sw.Write(sw.NewLine);
                     }
                     sw.Close();
                } 
            }
} 

Step 4:
Save the script in Script Task Editor and then close the window. You are all good to run the SSIS Package and it should dynamically create csv file for each of the excel sheet on excel files. 
I executed the Package and here are csv files which are created from 2 Excel files and four Sheets on those excel files.


CSV Files created from Excel Sheets from Multiple Excel Files in SSSIS Package by using Script Task






Things we learnt in this post
How to create variables in SSIS Package and use them in Script Task. 
How to read data from Excel File with Multiple Sheets in Script Task by using C# language
How to read data from DataTable and create csv file by using C# language
How to create csv file for each excel sheet in SSIS Package.



Check out our other posts/videos on 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



3 comments:

  1. Hi I am trying to run the code but showing error given below .... Please tell me why this type off error comes...

    Exception has been thrown by the target of an invocation.
    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
  2. Keep up the good work; I read few posts on this website, including I consider that your blog is fascinating and has sets of the fantastic piece of information. Thanks for your valuable efforts. convert pdf to png

    ReplyDelete
  3. Hi - Thank you so much for this video. I have .xlsx file with sheet names are Sheet1,Sheet2..
    I am getting the below error (I have added try-catch block to your c# script). Ironically, the same c# code is working if i rename the sheet names to lower case letters as sheet1, sheet2. Can you please suggest some work around to handle sheet names with upper case letters ?

    [Script Task Example] Error: External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
    at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.OleDb.OleDbConnection.Open()
    at ST_a3a8c14673e8402b8fea51441ac77f86.ScriptMain.Main()

    ReplyDelete