SSIS Part 155- How to Create Table per Excel File & Load all Sheets Data Dynamically in SSIS Package

SSIS Video Tutorial Scenario

How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task

Let's consider a situation when you receive single or multiple excel files in source(input) Folder. Each excel file can have single or multiple sheets. But all the sheets have the same columns. We need to create a new table if does not exists for each file and load the data to it. If table already exists we will be loading data from all the sheets to that table according to the name of excel file.

Link to the post for script used in the video

Check out our other videos in playlist on Excel Source and Destination in SSIS Package by using Script Task

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

Check out our website for SSIS,SSRS,SQL Server DBA and other tutorials

[pypr]

very good video. thank you so much for sharing a wonderful knowledge

[ajayganjoo]

Thanks for posting this video. It really solved my long pending problem ...

[samsoneromonsei]

I get this error when I run this code;


SSIS package Services Project2\Integration Services Project2\Package2.dtsx" starting.
Error: 0x1 at Script Task: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
Task failed: Script Task
Warning: 0x80019002 at Package2: SSIS Warning Code The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package Services Project2\Integration Services Project2\Package2.dtsx" finished: Failure.

[dite_academy]

I Tried to run this solution

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

this is the error i got