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

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.

Here are our sample files.

How to create Table for each Excel file and load data from all sheets in SSIS Package

Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.

Items we learnt in this post
How to create variable in SSIS Package for folder path and use in Script Task
How to use ADO.NET Connection in Script Task
How to create Table from Script Task in SSIS Package
How to create table by using Excel file name in SSIS Package
How to load the data to SQL server Table from Datatable by using C#
How to load multiple sheets data to Single SQL Server Table
How to loop through Excel files in Script Task and load to matching Table or create new table if not exists

there is consequently plenty in this article that i'd in no way have notion of occurring the order of for my very own. Your content gives readers things to assume pretty much in an engaging dependancy. thanks in your certain advice. Advanced Excel Training Mumbai

Is there a way to take use Foreach loop container and conditional split (instead of Script Task) to loop through multiple Excel files in a folder and then create new SQL tables where each new SQL table has the same name as each Excel file in the folder??