Scenario: Download Script
Let's say you get single or multiple Excel files in one of Source Folder. The Excel file can come with single or multiple sheets. The sheet names will be matching with the tables you have in your Database. You want to create an SSIS Package that should loop through Excel files and then Excel Sheets and load the data to table using Sheet Name as it is equal to Table Name.
If in future, you would like to add a new sheet data for existing table. The Package should be able to handle. Same way if you drop the sheet, it should be able to handle the scenario.
Here is my sample data. I have two Excel files, each contains two sheets, Product and Customer. Notice that the order of sheets does not matter. As long as the name matches with your Database tables we are good to load.
How to Load data from Excel Files to SQL Server Table as per Sheet Name dynamically in SSIS Package by using Script Task C# Scripting Language
Solution:
We are going to use Script Task in SSIS Package to handle this scenario for loading excel data from sheets as per sheet name to table.
Let's create Product and Customer tables after talking a look into our Excel Sheets.
CREATE TABLE dbo.Customer ( id INT ,name VARCHAR(100) ,dob DATE ) GO CREATE TABLE dbo.Product ( ProductId INT ,ProductName VARCHAR(100) )
I created the tables with dbo schema. You can change with different schema if you need to. Our SSIS Package is going to have a variable SchemaName so we can provide schema name for our tables.
Step 1: Create Variable in SSIS to Make your SSIS Package Dynamic
Create below variables in your SSIS Package
FolderPath: In this variable you will save the path from where you would like to read Excel Files
SchemaName: Schema of your Destination Table/s
Create Variables in SSIS To load Excel Data to SQL server Table by using Sheet Name for Table Name Dynamically - Script Task C# Scripting Language
Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task
Step3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.
Use Variables in Script Task in SSIS Package to Load Excel Dynamically to Different Tables as per Sheet Name
Step 4: Add Script to Script task Editor in SSIS To load Excel Data with File Name and Sheet Name
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; using System.Data.SqlClient;
Under public void Main() {
I have added below code.
String FolderPath=Dts.Variables["User::FolderPath"].Value.ToString(); String TableName = ""; String SchemaName = Dts.Variables["User::SchemaName"].Value.ToString(); var directory = new DirectoryInfo(FolderPath); FileInfo[] files = directory.GetFiles(); //Declare and initilize variables string fileFullPath = ""; //Get one Book(Excel file at a time) foreach (FileInfo file in files) { fileFullPath = FolderPath+"\\"+file.Name; //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(); TableName = sheetname.Replace("$", ""); //Load the DataTable with Sheet Data so we can get the column header OleDbCommand oconn = new OleDbCommand("select top 1 * from [" + sheetname + "]", cnn); OleDbDataAdapter adp = new OleDbDataAdapter(oconn); DataTable dt = new DataTable(); adp.Fill(dt); cnn.Close(); //Prepare Header columns list so we can run against Database to get matching columns for a table. string ExcelHeaderColumn = ""; string SQLQueryToGetMatchingColumn = ""; for (int i = 0; i < dt.Columns.Count; i++) { if (i != dt.Columns.Count - 1) ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'" + ","; else ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'"; } SQLQueryToGetMatchingColumn = "select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" + TableName + "' and Table_SChema='" + SchemaName + "'" + "and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList"; // MessageBox.Show(SQLQueryToGetMatchingColumn); //MessageBox.Show(ExcelHeaderColumn); //USE ADO.NET Connection SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection); //Get Matching Column List from SQL Server string SQLColumnList = ""; SqlCommand cmd = myADONETConnection.CreateCommand(); cmd.CommandText = SQLQueryToGetMatchingColumn; SQLColumnList = (string)cmd.ExecuteScalar(); //MessageBox.Show(" Matching Columns: " + SQLColumnList); //Use Actual Matching Columns to get data from Excel Sheet OleDbConnection cnn1 = new OleDbConnection(ConStr); cnn1.Open(); OleDbCommand oconn1 = new OleDbCommand("select " + SQLColumnList + " from [" + sheetname + "]", cnn1); OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1); DataTable dt1 = new DataTable(); adp1.Fill(dt1); cnn1.Close(); //Load Data from DataTable to SQL Server Table. using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection)) { BC.DestinationTableName = SchemaName + "." + TableName; foreach (var column in dt1.Columns) BC.ColumnMappings.Add(column.ToString(), column.ToString()); BC.WriteToServer(dt1); } } } }
Step 5: Run SSIS Package to Load Excel Data to SQL Server table/s according to Sheet Names
Save the script and Close the Script Task Editor. Run your SSIS Package. It should load the data from different Sheets in each excel file to SQL Server Table according to the sheet Name.
Here are my results for Customer and Product Sheet Names from two Excel files.
How to load Excel Data to SQL server Table according to Sheet Name in SSIS Package by using Script Task
Check out our other posts/videos for Dynamic Excel Source and Destination
- How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
- How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
- How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
- How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
- How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
- How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
- How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task
- How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
- How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
- How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
- How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
- How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
- Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
- How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
- How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task
- How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
- How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
- How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
- How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
- How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
- How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
- How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
- How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
- How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
No comments:
Post a Comment