Post reply

Using Dynamic SQLs stored in a table to execute in a loop for ETL

  • March 10, 2010 at 10:16 am

    #217656

    I need to populate a table using a dynamic SQL statements stored into another table. I have to pick up the SQLs from a table and execute them one by one and put the resultset data into another table. I want to pickup the SQLs into a variable and use this variable as SQLSource for the Data Source into a DFT.

    Any suggestions are welcome

  • March 11, 2010 at 5:26 am

    #1131946

    My first idea:

    read the table with the SQL statements with an Execute SQL Task and store the results in a recordset. This recordset is stored in an object variable.

    Then use a for each loop to loop over the recordset and map the SQL Statements to a variable.

    The only problem is that when the different SQL statements return different columns with different datatypes, the metadata is always screwed over, so that becomes problematic in the DFT.

    Maybe read the variable with the SQL stament in a script task and do all the handling there?

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

Viewing 2 posts - 1 through 1 (of 1 total)

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