Friday, November 20, 2009

Loop through excel sheets using SSIS



Control flow of the package once you complete Step1 to Step6 will look like:
Scenario:
I have 2 excel workbooks EPL.xls (with 3 excel sheets inside) and SPANISH
.xls (with 2excel sheets inside).All the 5 sheets have same number of columns with same name as well. I want to design a SSIS package to read all these 5 sheets and put all the records in a text file.
Declare 2 string variables ExcelPath and ExcelSheet in the scope of package.


Step1: Take for each loop which will loop through all the excel workbooks inside a particular folder. Edit for each loop as shown in the figure and click on Collection in left pane. My excel workbooks are in F:\MyStuff folder so I have used the location of the workbooks in the Folder option. Since I want to loop trough al the excel files, I have given *.xls in the Files option.

Step2:
Capture the excel workbook name in a string variable. I have defined a variable ExcelPath of type string. Click on variable mappings and select the ExcelPath variable from the drop down box with Index as 0 as shown in the following figure:
Step3:
Create an ADO.NET connection manager. Right click on the connection manager’s pane and select New ADO.NET Connection.Then click on New in Configure ADO.NET Connection Manager window and configure the connection manager as shown in the figure and click OK
Browse to an excel workbook in MyStuff folder as shown in the following figure:
Click All in the left pane and enter Excel 8.0 in Extended Properties and then click Test Connection to verify if the connection is successful. Now an ADO.NET connection manager is created with name as EPL. Go to properties of the EPL and click the ellipsis (…) against the Expressions.In the Property Expression Editor, select Connection String and set the expression as following:
"Data Source=" + @[User::ExcelPath] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
Step4: Now take one more For each loop inside the previous one and configure it as shown in the following figure:
Go to variable mappings and select ExcelSheet variable to capture the sheet name and set Index as 2.

Step5:
EPL.xls has 3 work-sheets : MANCHESTERUNITED,CHELSEA AND LIVERPOOL. Go to the variable ExcelSheet and set its value as CHELSEA$.Drag a Data Flow Task inside the inner for each loop. Take a Excel Source inside Data Flow Task and double click the excel source. Click New to create an excel connection manager using a valid excel file which has same structure as the excel files in F:\MyStuff folder. In this example I am taking EPL.xls which is sitting in F:\MyStuff folder. Check the "First row has column names" check box and click OK. Select Data Access Mode as Table Name or Vie Name variable and select the ExcelSheet variable from the "Variable Name" drop down box .
Click on Columns in Excel Source edotor to verify the column names and then click OK. Go to the properties of Excel Connection Manager and use the ellipsis (…) against the expressions to set the Connection String property using following expression:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelPath]+";Extended Properties=\"" + "Excel 8.0;HDR=YES\"" + ";"

Step6:
I will capture all the records from the sheets into one text file. I have created a text file Information.txt. Then grab a Flat File Destination and double click on it to open the editor. Uncheck the "Overwrite data in File" option and click New to create a flat file connection manager. Verify that the Delimited radio button is selected and then click OK. Configure the Flat File connection manager editor as shown in the following figure:
Click on columns then mappings and click OK. Now we are ready to execute the package.

6 comments:

  1. This is a great article. Many thanks Nitesh for posting! I could not find a solution to this problem anywhere else on the Internet. Before seeing this article I was starting to write custom VB script.

    Regards,
    Chris

    ReplyDelete
    Replies
    1. Hi Chris,

      Nice to hear that it helped you

      Delete
  2. Thanks a lot
    It was highly useful.

    Regards,
    Ami

    ReplyDelete
  3. Thanks a lot
    It is really really help full to me
    Thank you once again

    ReplyDelete
  4. I am facing this error while doing this

    Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)". This occurs when the ForEach Enumerator cannot enumerate.

    ReplyDelete
  5. hi prakash use sampackage and do small modfication goto project properties and debugging tab and change 64 bit false then you try this.

    ReplyDelete