Post reply

Dynamic SQL in SSIS

  • August 9, 2011 at 3:04 pm

    #241594

    Hi All,

    I have a meta data table that stores settings for generating files for different users. Columns in the table are filepath, archive file path and sql statement.

    Example of the values in the table:

    User1 c:\test SELECT 'ABC' AS A, 'BCD' AS B

    User2 c:\test SELECT 'DEF' AS M, 'BCD' AS N

    I created an SSIS package that reads the columns from this table and stores the values into variables. I do this by using an Execute SQL task to get the number of users I will be processing and then executing a foreach loop container.

    I then need a method to run the SQL statement in the SQL column to generate a file. The columns in the sql statement will be differnt for most users.

    I am running into problems using an OLE DB Source in a Data Flow Task because I get meta data errors:

    VS_NEEDSNEWMETADATA

    I am trying to think of the best way to implment this. Basically, I need a method to run dynamic sql commands through different iterations of a loop container.

    I appreciate any asstance.

    thanks,

    Radro

  • August 9, 2011 at 4:06 pm

    #1366318

    This seems to be a pretty good explanation of passing variables into and out of the Execute SQL task.

    http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

    You may be getting the metadata error because of having column names that change. I don't think SSIS likes that.

  • August 10, 2011 at 3:58 am

    #1366450

    You can't configure the dataflow to be dynamic, at least not out of the box.

    So you can do it with scripting in a .NET script task or using some tool like the bcp utility.

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

  • August 11, 2011 at 3:49 pm

    #1367632

    As Koen said, out of the box it is not possible. The standard Data Flow Task doesn't support dynamic data flows.

    If you can use third-party solutions, check the commercial Data Flow Task Plus. It is an extension of the standard Data Flow Task with support for dynamic data flows at runtime. You can implement your requirement only with one For Each Loop Container and one Data Flow. The solution doesn't require programming skills.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • August 11, 2011 at 4:14 pm

    #1367642

    How many permutations of sql statements do you envision need to be run?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 24, 2019 at 2:52 pm

    #2025308

    radro23 - Tuesday, August 9, 2011 3:04 PM

    Hi All,I have a meta data table that stores settings for generating files for different users. Columns in the table are filepath, archive file path and sql statement.Example of the values in the table:User1 c:\test SELECT 'ABC' AS A, 'BCD' AS BUser2 c:\test SELECT 'DEF' AS M, 'BCD' AS NI created an SSIS package that reads the columns from this table and stores the values into variables. I do this by using an Execute SQL task to get the number of users I will be processing and then executing a foreach loop container.I then need a method to run the SQL statement in the SQL column to generate a file. The columns in the sql statement will be differnt for most users. I am running into problems using an OLE DB Source in a Data Flow Task because I get meta data errors:VS_NEEDSNEWMETADATAI am trying to think of the best way to implment this. Basically, I need a method to run dynamic sql commands through different iterations of a loop container.I appreciate any asstance.thanks,Radro

    check this video it my help you 
    SSIS Dynamic SQL Command

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

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