SSIS SCENARIO

  • Hi All

    I want to extract data from multiple Excel sheets and then create different tables for that data in the SQL server and load that data into the respective table. I want to do this using SSIS. I know we can do this by creating separate package for each file but is there any way we can do this operation in a single package?

    Looking for this type of solution

    File1... file n ---extract data--> SSIS ---createtablewithvar-->SQLSERVER tab1 .. tab n

    Thanks in advance....

  • sql.abhishek (9/7/2010)


    Hi All

    I want to extract data from multiple Excel sheets and then create different tables for that data in the SQL server and load that data into the respective table. I want to do this using SSIS. I know we can do this by creating separate package for each file but is there any way we can do this operation in a single package?

    Looking for this type of solution

    File1... file n ---extract data--> SSIS ---createtablewithvar-->SQLSERVER tab1 .. tab n

    Thanks in advance....

    You'll need to generate the SQL Server tables in advance. If they don't exist, you can't use them as destinations in your data flows.

    Iterating around a collection of files is not difficult - you'll probably use a Foreach loop to do that. But if every file has a different structure, you'll need a separate data flow for each.

    A single package can definitely do this, though I suspect not in the dynamic way you desire.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thx Phil ...

    I know but i am just curious about makin it dynamic .... but i am not able to think of a way to do so ...

  • I can think of one way:

    1) Write some code that analyses the Excel sheets and does the DDL stuff for you.

    2) Write more code which builds your package, using the newly created tables from (1).

    3) Test - after a few days, you might be getting close 🙂

    All in all, a complete nightmare. Don't do it unless you have to.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil is right. Don't bother to make it dynamic if there are only a handful of Excel files. However, if there are a lot of Excel files (say more than 20 or 50), each with a different structure, then you can try the dynamic approach.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply