How to read Multiple Excel Sheets in one Excel File in SSIS Part -1

CREATE AN EXCEL FILE THAT SHOULD CONTAIN MULTIPLE EXCEL SHEETS. PLEASE FIND THE SAMPLE EXECL SHEETS BELOW. HERE I AM USING EXCEL 2007.

Create four variables, such as FilePath, FilePattern, FolderPath and SheetName.  Please find the screen shot below.

In the Connection Manager of the Package (Bottom of the package) create an ADO.NET Connection Manager with Configuration settings below. Name it as OrderExcelMasterSchema.

Select the Provider Name from the connection Manager List; Such as “Microsoft Office 12.0 Access Data Base OLEDB Provider. Click OK button and then enter Server Name .

Next Click on ALL Tab which is resides on left hand side of in the Connection Manager.  Click on that. It displays the properties of Connection Manager. Please find the Screen shots below.

In the Properties of Connection Manager: Enter EXCEL 12.0(We are using Excel 2007. If it is Excel 2003 then we need to write Excel 8.0) in Advanced Column. Then click on Test Connection. Please find the screen shot below.

Then Click on OK button of Connection Manager and then Click on OK button of Configure  ADO.NET Connection Manager Window.Then in the Same way create an Excel Connection Manager .Please find the screen shot below.

Click on ADD button. Then Excel Connection Manager Window will be displayed. Then click on BROWSE Button of Excel Connection Manager Window. Select the path of Excel file. Please find the Screen shots below.

Then Click on OK Button of Excel Connection Manager.In the Same way Right Click on the Connection Manager of Package add OLEDB Connection Manager for SQL Server .Please find the Screen shot below.

Click on New OLEDB Connection Manager. It displays Configure OLEDB Connection Manager. Click on New Button.

Please Check for Second Link for Continue.....

How to read Multiple Excel Sheets in one Excel File in SSIS Part-2