Variable mapping - For Each Loop

  • Metadata Table (SQL ServerDB Table)

    COLUMNSINPUT TABLEColumn1Column2OUTPUT TABLE

    --------------------------------------

    TableAColName1NULLTableD

    TableAColName2NULLTableE

    TableAColName3NULLTableF

    TableBColName4ColName1TableG

    TableBColName5NULLTableH

    TableBColName6NULLTableI

    Variables created:

    INPUT TABLE - @in_table

    Column1 - @col1

    Column2 - @col2

    OUTPUT TABLE - @out_table

    Requirement:

    1. I need to loop through the Metadata table and store the values in variables

    2. Use the variables in SQL statements, examples of 2 SQL statments are below:

    1. INSERT INTO dbo.Tmp_Ref

    (SELECT column1, dbo.UDF_Refer_Col2_Text(' Column1 + ', ' + Column2 + ') FROM @in_table)

    2. DELETE dbo.Tmp_Ref WHERE Column1+''£££''+Column2 IN (SELECT [ColA]+''£££''+[ColB] FROM @out_table)

    3. INSERT INTO @out_table ([ColA], [ColB]) (SELECT Column1, Column2 FROM dbo.Tmp_Ref)

    Issue:

    I know I can store the metadata table into a ADO variable via a recordset.I can then use a For Each Loop to map the variables to the columns.

    I am not sure how I can 'map' the variables to the SQL query. I can only refer to variables using '?'

    Eg:

    INSERT INTO dbo.Tmp_Ref

    (SELECT column1, dbo.UDF_Refer_Col2_Text(' Column1 + ', ' + Column2 + ') FROM @in_table)

    how could I map the variables @col1 and @col2 to Column1 and column2?

    Any help would be appreciated.

  • I am assuming you are using the Execute SQL Task. You can map variables to the ? in the order they appear on the Parameter Mapping tab.

    I see that you are not using the variables as true parameters, but more like dynamic SQL builders, so I would probably take a different approach. I would create a variable to build your entire SQL Statement, not just the dynamic portions of it, and then change the SQL Source type in the Execute SQL Task from Direct Input to Variable.

  • Hi Daniel,

    Many thanks for your reply.

    I was using an Execute SQL within the For Each Loop.

    Could you please give an example of the 'variable to build your entire SQL Statement'. I am not sure of how to progress with the actual statement. If you could provide some guidance I would be most grateful.

  • First take a look at Andy Leonard's blog on dynamic SQL in variables

    http://sqlblog.com/blogs/andy_leonard/archive/2007/07/22/ssis-design-pattern-dynamic-sql.aspx

    In his blog he demonstrates the creation of the dynamic SQL in a variable and then uses a script task to display the resulting SQL. You don't really need to do that, he just did it to show the resulting script. But if you follow his steps to the point of the script task you will have your dynamic SQL built. Looking at your post you may need several variables, and I am not sure how you would distinguish which dynamic SQL variable to call with the information you gave.

    Anyway if you stop just before the script task and then start with your Execute SQL Task. On the General Tab, set your "SQLSourceType" to Variable, and then your "SourceVariable" to the appropriate variable. Everything else in your Execute SQL Task should be pretty much the same as before. One caution though, SSIS variable expressions are limited to 4000 characters. If you have really long SQL statements you may bump into that limit. There are ways around that, but you don't really want to cross that bridge until you need.

  • Thank you this is very interesting .

    Like you said I@m not sure how to map to the variables.

    Is there anyway of being able to distinguish between the '?' variable mappings ..

  • How many basic SQL statements will you have? Perhaps you could use an expression based precedence constraint to channel the program flow to the correct Execute SQL Task for the need.

    As far as distinguising the ?, you simply list your variables in the order in which the ? appear in your SQL. So if your SQL is something like this

    SELECT ?

    FROM table

    WHERE ?

    AND ?

    You list the variable mappings so that the first one in the list is for the SELECT ?. The second one in the list is for the WHERE ? and the third one in the list is for the AND ?

  • What's up,

    I did not understand completely the trhead , but is this something similar to what you want?. If it is, then we can make it perform you requirements.

    DECLARE @MetaData as table (InputTable varchar(50), Column1 varchar(50), Column2 varchar(50),OutputTable varchar(50))

    INSERT INTO @MetaData

    SELECT 'TableA', 'ColName1', 'NULL', 'TableD' UNION

    SELECT 'TableA', 'ColName2', 'NULL', 'TableE' UNION

    SELECT 'TableA', 'ColName3', 'NULL', 'TableF' UNION

    SELECT 'TableB', 'ColName4', 'ColName1', 'TableG' UNION

    SELECT 'TableB', 'ColName5', 'NULL', 'TableH' UNION

    SELECT 'TableB', 'ColName6', 'NULL', 'TableI'

    DECLARE @SqlCommands AS VARCHAR(MAX)

    SET @SqlCommands = ''

    SELECT @SqlCommands = @SqlCommands + 'INSERT INTO '+OutputTable+' SELECT '+Column1+' FROM '+InputTable+';'+ CHAR(13)+CHAR(10)

    FROM @MetaData

    PRINT @SqlCommands

    --EXEC @SqlCommands

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

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