Load Multiple Excel Sheet Data To Table Using SSIS

Load Multiple Excel Sheet Data To Table Using SSIS

Today we will see how to Load data from multiple Excel sheet to SQL server table using SSIS.
we need to create one excel file with some sheets and Data.I have created one Excel file with 3 sheets.
Excel Input
We need to use For each loop for this task.
Open SSDT [Sql Server Data Tools], create a new project.
Right click on Package and click on create new Package.
for next , go to toolbox ->Container ->For each Loop Container.double click on it.
foreach loop editor
for each loop container
Now double click on For each loop container. For loop, the editor will open. go to Collection. click on Enumerator drop own box and select Foreach ADO.NET Schema Rowset Enumerator as we want to extract data from every sheet of excel file.
For schema choose Tables.
Now click on Connection and click on New Connection.

 

foreach loop editor
For each loop editor
From Connection Manager, you will see Provider. we need to choose Microsoft Office 12.0 Access Database Engine OLE DB Provider as we want to extract data from Excel.
now go to All, you will see the option for Advance, in Advance there is an Extended Properties write down below code into Extended properties.
Excel 12.0 XML;HDR=YES
Click on O.k .now comes to Connection, Enter the Excel File path in a file name. and test connection

 

Connection
Now we will add one script task in which we will add one message box which will print the sheet name from the Excel file. Go to  SSIS toolbox ->Common ->Script Task. Add the script task in to for each loop container. For printing sheet name we need to store sheet name into some variable. double click on for each loop container and go to variables. now create a new variable  and give the name as “SheetName”

 

SSIS variables
Go to script task double click on it and go to Expression. From Properties choose to read only variables and click on it. from select variable choose Sheet Name variable which we have created in the last step.

 

Variable
Now click on Edit script and write down below code into the script.

MessageBox.Show(Dts.Variables[“User::SheetName”]).Value.Tostring());

Now save and execute a package. the message box will open 3 times with sheet name as we have 3 sheets in excel file

 

Result
Now we will add data from excel to SQL table.for that go to SSIS toolbox ->Data Flow Task .double click on it.
In the data flow task choose ADO.NET Source from other Source. click on the new connection and give the path of excel file. and choose one sheet.

 

Select Sheet
Now we need to add OLEDB destination.click on add new connection and select database table in which we want to load data. Go to mappings. Map input column with destination Column.

 

Compare Data Types
The data type of input column and Destination column will differ we need to make it same. for that add data Conversion Transformation.Go to toolbox ->common ->Data Conversion. double-click on it and select the column for which we want to change data type .change the data type of column. we need to remap the input column with Destination column.

 

Mapping
Now Execute package .the whole data of Excel will be load in SQL Table.

 

SSIS Data Conversion

 

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *