none
SSIS / Excel integration RRS feed

  • Question

  • Hi,

    Is it possible to use SSIS to load excel data across several worksheets with different designs from the same workbook? If this isn't possible what is the best alternative method to extract the sheet data so that it can be loaded?

    • Would BCP work across multiple worksheets?
    • Would I need to implement a package per worksheet?
    • Another alternative could be to use VBA or C# and programmatically build the logic.

    I need to do this programmatically with no user actions. Any advice/links would be great.

    Seems like a big limitation in SSIS if different worksheets with different designs cannot be implemented via a single SSIS package.

    Thanks.

    Tuesday, December 5, 2017 5:01 PM

All replies

  • Hi Mikejh,

    In my opinion, the best way is to implement a package per worksheet.

    Have a look to this article, it explains how to do it :

    http://www.techbrothersit.com/2015/03/load-multiple-sheets-from-multiple.html


    Please mark as answered, If you feel happy with this answer.

    Tuesday, December 5, 2017 5:07 PM
  • Yes, it can be done in SSIS as long as you can determine what format a worksheet is in via the tab name. You can then either use precedence constraints or enable/disable to send it to a task that recognises the format of that worksheet
    Tuesday, December 5, 2017 7:29 PM
  • Hi Mikejh03,

    So, would you like to load multiple worksheets into multiple destinations?

    If you need to do this programmatically, as the designs are different which means you need to map columns dynamically. But as far as I know there are no components to allow us map columns dynamically in Data Flow Task. So, I think you may try to use Script Task with VB/C# to loop all worksheets then use SqlBulkCopy to load data into corresponding table.

    Please refer to this similar sample code snippet: how to import multiple excel sheets into 2 sql server tables? 

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 6, 2017 2:50 AM
    Moderator
  • By different design I assume you meant different metadata. In that case can you atleast determine it beforehand? If yes, you could setup multiple data flow tasks to point to different sheets of your excel to do the data transfer. Even if number of sheets may vary, if metadata is consistent you can implement a For Each loop using SChema Rowset enumerator to iterate through the various sheets.

    See this for more details

    https://www.mssqltips.com/sqlservertip/4157/how-to-read-data-from-multiple-excel-worksheets-with-sql-server-integration-services/

    However, in case of varying metadata, you've to generate data flow also programmatically

    http://blogs.selectsifiso.com/programmatically-create-data-flow-task-inside-a-sequence-container-using-c/

    This will ensure it generates metadata based on your sheet and then do your data transfer


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 6, 2017 3:45 AM
  • Yes, it can be done in SSIS as long as you can determine what format a worksheet is in via the tab name. You can then either use precedence constraints or enable/disable to send it to a task that recognises the format of that worksheet
    Yes the worksheet name will drive the format. I will look into this route some more - thanks. Do you know of any good links I can use?

    Wednesday, December 6, 2017 4:17 PM
  • Yes, it can be done in SSIS as long as you can determine what format a worksheet is in via the tab name. You can then either use precedence constraints or enable/disable to send it to a task that recognises the format of that worksheet

    Yes the worksheet name will drive the format. I will look into this route some more - thanks. Do you know of any good links I can use?

    The first link Visakh posted will get you most of the way, then based on the value of the SheetName variable, you can enable/disable the appropriate tasks (there would obviously be multiple tasks which isn't covered by the example), the important thing to ensure is that only one of your tasks is enabled for any 1 sheet
    Wednesday, December 6, 2017 7:49 PM
  • Yes, it can be done in SSIS as long as you can determine what format a worksheet is in via the tab name. You can then either use precedence constraints or enable/disable to send it to a task that recognises the format of that worksheet

    Yes the worksheet name will drive the format. I will look into this route some more - thanks. Do you know of any good links I can use?

    The first link Visakh posted will get you most of the way, then based on the value of the SheetName variable, you can enable/disable the appropriate tasks (there would obviously be multiple tasks which isn't covered by the example), the important thing to ensure is that only one of your tasks is enabled for any 1 sheet

    Thanks RyanAB. This looks like a promising approach, but I need some more pointers - sorry. (I am a beginner in the SSIS world.)

    So in my head I have the following for Control Flow … (hope you can follow)

    1) A “ForEach Loop Container” containing

    2) a “Master task(?)” which reads the workbook and then

    3) branches (using precedence) for worksheet A/B/C/D based on worksheet name where

    4) each branch contains a dataflow specific to the worksheet being handled.

    So is this correct? (Probably not 😊.) Also, how is the "Master task" implemented?

    Many thanks for your guidance.


    Friday, December 8, 2017 3:42 PM
  • Hi Mikejh03,

    So, would you like to load different sheets into different tables?

    The standard Data Flow Task supports only static metadata defined during design time. You have to create separate data flow for each sheet metadata and table you want to process.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 12, 2017 6:12 AM
    Moderator
  • Just sharing the answer to my question...

    So after some playing around the approach below worked for an excel workbook with several different worksheets with different meta data designs. Here are the constraints: (I think these would be common in warehouse type projects)

    • although the worksheets have different meta data designs they are static (ie no dynamic meta data)
    • each worksheet has a specific target table
    • a "master" data flow is needed (this is attached to the parent worksheet)

    Hope this helps (shame I cant paste a screenshot as this would have been ideal)

    1) A “ForEach Loop Container” containing

    2) a “Master" data flow task which is linked to the primary worksheet in the workbook. This task doesn't write any data but is just used to control the flow of data based on worksheet name. This task then 

    3) branches (using precedence) for each worksheet to be processed based on worksheet name where

    4) each branch contains a specific data flow task. This is the task(s) that read/write the worksheet data.

    Let me know if you have any comments on the above and thanks to all that helped.

    Wednesday, December 13, 2017 10:14 AM
  • Hi Mikehg03,

    Thanks for sharing the solution.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 15, 2017 7:23 AM
    Moderator