How will you design your package to load all of the sheets to same table?

0 votes
asked Nov 17, 2015 in SSIS by Sunain (420 points)
We have received Excel Source File with multiple sheets but with the same meta data.How will you design your package to load all of the sheets to same table?

1 Answer

+1 vote
answered Dec 3, 2015 by darsh (3,020 points)

Step 1:
Create connection to our Excel source file as shown below

Choose Microsoft Office 12.0 Access Database Engine OLE DB Provided as shown below

Press Ok as shown above in number 3. Provide the File Path as shown below

Click on All and Provide values as shown below

We are all done with configuring our Excel File Connection.

Step 2:
Bring For each loop container to the Control Flow Pane and configure as shown below
Enumerator : Foreach ADO.NET Schema Rowset Enumerator
Connection String : The OLE DB Connection String that we have created in Step 1
Schema : Tables As we are interested to get the names of sheets

Go to Variable Mapping and map the variable to Table_Name as shown below

Set Index from 0 to 2 as Table_Name is on third number in List

Step 3:
Bring Data Flow Task to Control Flow pane inside Foreach Loop Container. Open Data Flow by double Clicking and then bring ADO.NET Source as shown below and configure. One thing to notice here, we do not have option to provide variable in which we have saved our SheetName , For now Choose any sheet you like as all Sheets in Excel are with same metadata.

Drag Derived Column Transformation to Data Flow pane and Connect ADO NET Source to it. Perform data conversation according to destination columns and also generate new column DER_SheetName by using SheetName variable.

After that bring OLE DB Destination and connect Derived Column Transformation to it and map the columns.

Step 4:
Till here our package is ready but only Table Name(SheetName) is hard coded in our ADO NET Source. We want to change it to SheetName Variable so with each iteration it should change so we can load all the Excel sheets.
To do that , Click on Data Flow Task and go to properties and then Expression and configure as shown.

Set the data flow property Delay Validation=True so it should not validation as sheet names will be provided while execution not at start of package validation.

Final Output :
Execute package and check the destination table if all the records from all excel sheets are loaded successfully.

Welcome to I Can Crack, where you can ask questions and receive answers from other members of the community.

68 questions

47 answers

3 comments

5,318 users

...