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;
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 = SourceFolder); //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
Check out our other posts/videos