locked
Dynamically pick the table names in data flow task SSIS RRS feed

  • Question

  • Hi All,

    I want to create a SSIS package which loads the data to a table on the other server every day. I have around 250 tables to load everyday and source and destination table names are available in a metadata table, table names have to read from the metadata table and data should be loaded. Is there a way that we can configure the source and destination table names dynamically in Data flow task?

    I am newbie to SSIS can any help with the solution for this problem.

    Tuesday, April 22, 2014 2:12 PM

Answers

  • You can do that, not a big deal. The underlying problem is say suppose you constructed a ETL based on some x source and y destination and have put x(3 columns) as source and y destination(3 columns).. As you said if we have choice of dynamically pick the table names.. ETL might fail when you face below situation

    source x(3 columns)   destination y (4 columns) and there will be no mapping as it is dynamic. Sometimes mapping also fails even if the source and destination have same number of columns. If you still want to do... follow below steps:

    Create two variables:

    1.variable1 , datatype string

    2.variable datatype string  

    take one execute sql task, pick your source table names dynamically as you desired from metadata table \

    "SELECT sourcetblname as Res FROM @metadata WHERE ID=1" in sql statement and then go to name the result name as Res (I meant same as table alias) and map it to variablename1

    And in variable2 go to expression and write "Select * from "+@[User::variable].. and this will be your constructed dynamic command for oledb destination.

    And connect that execute sql task to (Data flow task)oledb source and choose data access mode as sql command with variable,and choose variable2. below is the diagram.


    - please mark correct answers


    • Edited by Murali dhar Tuesday, April 22, 2014 6:55 PM
    • Proposed as answer by Mike Yin Friday, April 25, 2014 3:04 AM
    • Marked as answer by Mike Yin Sunday, May 4, 2014 3:36 PM
    Tuesday, April 22, 2014 6:55 PM