none
regarding ssis - Read multiple excel sheets from one excel file RRS feed

  • Question

  • Hi,

    We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?


    Sunday, September 13, 2015 5:48 PM

Answers

  • You can use a Foreach Loop Container to loop to Excel sheets, but does the tenth sheet has a distinguishable name compared to the first 9?


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

    Sunday, September 13, 2015 7:32 PM
    Moderator
  • Hi Koteswararao,

    If we want to use a Foreach Loop Container with one Data Flow Task to achieve your requirement like this link, then all the sheets follow the exact same structure in your excel file. But in your scenario, it doesn’t fit the rule because of that the tenth file contains error data. If you can, we can split the tenth sheet in another excel file, then delete the tenth sheet from the original excel file, and then use a Foreach Loop Container with one Data Flow Task to load the first nine sheets and use another Data Flow Task to load the tenth sheet.

    If it is not, then we may need to use multiple Data Flow Tasks, one for one file extracting data. Because Data flow task doesn't support dynamic metadata.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, September 14, 2015 7:53 AM
    Moderator

All replies

  • You can use a Foreach Loop Container to loop to Excel sheets, but does the tenth sheet has a distinguishable name compared to the first 9?


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

    Sunday, September 13, 2015 7:32 PM
    Moderator
  • Hi Koteswararao,

    If we want to use a Foreach Loop Container with one Data Flow Task to achieve your requirement like this link, then all the sheets follow the exact same structure in your excel file. But in your scenario, it doesn’t fit the rule because of that the tenth file contains error data. If you can, we can split the tenth sheet in another excel file, then delete the tenth sheet from the original excel file, and then use a Foreach Loop Container with one Data Flow Task to load the first nine sheets and use another Data Flow Task to load the tenth sheet.

    If it is not, then we may need to use multiple Data Flow Tasks, one for one file extracting data. Because Data flow task doesn't support dynamic metadata.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, September 14, 2015 7:53 AM
    Moderator
  • 1. Using foreach loop container for ado.net enumerator, get the excel sheets.

    2. Add data flow task. Add oledb source to read each excel sheet. Add oledb destionation to push these source excel records.

    3. Configure error output to redirect rows to another error oledb destination.



    Supriya Thigale

    Monday, September 14, 2015 10:14 AM