none
Reading Multiple Worksheets from a spreadsheet with a single OLEDB Connection Manager(Excel).

    Question

  •  

    Hi all,

     

    Any idea about how to configure/read multiple worksheets from a spreadsheet using single connection manager?

    I think using SQL Command we could able to do - not sure how to achieve that. Let me know the other alternatives too.

    Thank you for any help Smile

     

    - Chennoju

     

     

    Thursday, October 04, 2007 11:32 AM

Answers

All replies



  • I am not sure what are you trying to accomplish; but as far as I know the excel connection manager points to the excel file. It is in the source/destination component where you indicate which sheet(table) want to point to. So, if you want 2 sheets from the same file in a dataflow; then you have use 2 Excel source components. You could also access data from excel via Execute sql task.
    Thursday, October 04, 2007 12:09 PM
    Moderator
  •  

    The Excel Source supports a "SQL command" option, and JET supports the UNION ALL operator. So try this syntax, it works for me-

     

    SELECT * FROM [S1$]
    UNION ALL
    SELECT * FROM [S2$]

     

    I expect the usal UNION caveat applies, the table (sheet) definitions must match.

    Thursday, October 04, 2007 12:23 PM
    Moderator
  •  

    Thanks for the quick response. The issue is, we have around 30 worksheets in a spreadsheet and each one contains around 5000 records. The name of the worksheet is not a static one, it keeps changing with some number.

    Eg: Name of the worksheet : DD720X8ABB9000218210

       MA870X8ABB9000218210

    Is there any best way to solve this problem?

    One more issue is, I need to skip the first 8 rows from the spreadsheet. ( this problem solved using the OpenRowSet method for a single worksheet,  how can I use this while joining the Worksheets?

     

    Thank you for any help.

     

    - Chennoju

    Thursday, October 04, 2007 1:31 PM
  • The Union method will not work.

     

    If you can find a method of enumerating sheets it would allow you to drive a ForEach loop over them, and import each sheet. I thought there was a hidden metadata table that listed sheets/tables but cannot see it today.

    Thursday, October 04, 2007 4:14 PM
    Moderator
  • Well, BOL explains how you can do it; but for some reason it is not working for me. I am getting the error:

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

    Would somebody try and let me know?

    From BOL:

    To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator
    1. Create an ADO.NET connection manager that uses the Microsoft Jet OLE DB Provider to connect to an Excel workbook. On the All page of the Connection Manager dialog box, make sure that you enter Excel 8.0 as the value of the Extended Properties property. For more information, see How to: Add a Connection Manager to a Package.

    2. Create a string variable that will receive the name of the current table on each iteration of the loop.

    3. Add a Foreach Loop container to the Control Flow tab. For information about how to configure the Foreach Loop container, see How to: Configure a Foreach Loop Container.

    4. On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.

    5. As the value of Connection, select the ADO.NET connection manager that you created previously.

    6. As the value of Schema, select Tables.

      Note:
      The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. If you have to filter the list for only worksheets or only named ranges, you may have to write custom code in a Script task for this purpose. For more information, see Working with Excel Files with the Script Task.

    7. On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.

    8. Close the Foreach Loop Editor.

    9. Create tasks in the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook. If you use a Script Task to examine the enumerated table name or to work with each table, remember to add the string variable to the ReadOnlyVariables property of the Script task.



    Thursday, October 04, 2007 5:16 PM
    Moderator
  • hmm, I am also getting the same error , but with little different error message:

     

    Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80070057
     "The parameter is incorrect.". This occurs when the ForEach Enumerator cannot enumerate.

     

    what could be the issue? experts please look into this issue.

    Sample Excel file: Sample.xls

     

    1. Excel file with 10 worksheets

    2. Each work sheet name is different.

    3. Each worksheet contains 3000 records

     

    Thanks for any help.

     

    -Chennoju
    Friday, October 05, 2007 10:09 AM
  •  

    I got it.  Working with Excel Files with the Script Task:

    The following link worked for me. http://technet.microsoft.com/en-us/library/ms403358.aspx

    Example 4 is the suitable one for my requirements.

     

    - Chennoju.

     

     

    Saturday, October 06, 2007 6:10 AM
  • chennoju

    ive run into the problem you had earlier...except im not certain how to modify the existing script to get the functionality....could you please help?
    Tuesday, January 20, 2009 10:47 PM
  • Hi !

     

    I have the same issue...

     

    First time it worked.... but now its not working...

     

    Pls let me know the problem am facing here


    Regards,
    Sunday, January 15, 2012 3:39 AM
  • Here are some examples to loop through Worksheets within an excel file. For example with a Script Task:

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, July 06, 2012 12:54 PM
    Moderator
  • Well, BOL explains how you can do it; but for some reason it is not working for me. I am getting the error:

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

    Would somebody try and let me know?

    From BOL:

    To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator
    1. Create an ADO.NET connection manager that uses the Microsoft Jet OLE DB Provider to connect to an Excel workbook. On the All page of the Connection Manager dialog box, make sure that you enter Excel 8.0 as the value of the Extended Properties property. For more information, see How to: Add a Connection Manager to a Package.

    2. Create a string variable that will receive the name of the current table on each iteration of the loop.

    3. Add a Foreach Loop container to the Control Flow tab. For information about how to configure the Foreach Loop container, see How to: Configure a Foreach Loop Container.

    4. On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.

    5. As the value of Connection, select the ADO.NET connection manager that you created previously.

    6. As the value of Schema, select Tables.

      Note:
      The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. If you have to filter the list for only worksheets or only named ranges, you may have to write custom code in a Script task for this purpose. For more information, see Working with Excel Files with the Script Task.

    7. On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.

    8. Close the Foreach Loop Editor.

    9. Create tasks in the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook. If you use a Script Task to examine the enumerated table name or to work with each table, remember to add the string variable to the ReadOnlyVariables property of the Script task.




    Rafael Salas Where you able to resolve the issue in this appraoch i am also rying the same and getting the same error as you have mentioned

    Abhinav
    http://bishtabhinav.wordpress.com/

    Friday, August 17, 2012 6:52 AM
    Moderator