Load Multiple Excel Sheet Data To Table Using SSISTechExtensor
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.
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.
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.
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
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”
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.
Now click on Edit script and write down below code into the script.
Now save and execute a package. the message box will open 3 times with sheet name as we have 3 sheets in excel file
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.
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.
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.
Now Execute package .the whole data of Excel will be load in SQL Table.