SSIS Part 154 -How to create Table for each sheet in Excel Files and load data to it dynamically

SSIS Video Tutorial Scenario
You have tons of Excel files sitting in your source folder. Some of the Excel file has single sheet and some of them have multiple sheets. You got this requirement to create table for each of the sheets and load the data. As the tables are going to be dropped and created on each execution in staging database, you are going to use NVARCHAR(4000) for all the columns in your tables.

Once the data is loaded to staging database, you will have another process to read and convert the data to correct data types and load to your final destination tables.

The naming convention you will be using for the Table will be FileName_SheetName.

Link to script used in the video

Things we learnt in this Videos
How to use variable in SSIS Package
How to use ADO.NET Connection manager in Script Task in SSIS Package
How to drop and create new table in SQL Server Database from Script Task by using C#
How to load data from Excel Sheet to SQL Server Table by using C# in Script Task in SSIS Package

Check out our SSIS Video Tutorial List to watch related Excel SSIS Videos on below topics

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

[sonjoysengupto]

Thanks a lot for sharing, very eloquent demo.
Your C# script could have been written a bit more optimally but that's really not the focus of this demo, so understood🙂

[chaitu]

for me i am getting an error exception has been thrown at the time of invocation
at target, Object[] arguments, Signature sig, Boolean constructor)
at obj, Object[] parameters, Object[] arguments)
at obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at