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

Select the Server name and Database name then click on Test Connection Button. Please find the screen shot below.

Then Click on OK Button of Connection Manager and OLEDB Configuration Manager Window. And Rename it as SQLSERVER CONN. Then we can have Three Connection Managers in Package. Please find the Screen shots below.

We need to do the following connection string changes  So that the Excel file is dynamically changed as the files are looped through.

Select the OrderMasterSchema from the List of Connection Managers and then Right Click on the OrderMasterSchema Select Properties. Then Properties window will be displayed. Please find the screen shot below.

Click on Expression in the Properties window. Please find the screen shot below. Click on the Ellipse button. Then Properties Expression Editor will be displayed.  In the Property tab, Select Server Name  and in the Expression Window Click on Ellipse button. Then it will display Variable Window. Drag the  variable to Expression Window. Please find the screen short below.








Click on OK Button of Expression Builder windows and the Property window. Then Property window will be looks like in the below Screen shot.





Similarly we have to make to for Excel Connection Manager. Please find the Screen shots below.



Select Excel Connection Manager from the List of Connection Managers and then Right Click on the Excel Connection Manager Select Properties. Then Properties window will be displayed.

Click on Expression in the Properties window. Please find the screen shot below. Click on the Ellipse button. Then Properties Expression Editor will be displayed.  In the Property tab, Select Server Name and in the Expression Window Click on Ellipse button. Then it will display Variable Window. Drag the variable to Expression Window. Please find the screen short below.

 


 


Please click here for part-3

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