Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Variable mapping - For Each Loop


Variable mapping - For Each Loop

Author
Message
AarionSSQL
AarionSSQL
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 253
Metadata Table (SQL ServerDB Table)


COLUMNS INPUT TABLE Column1 Column2 OUTPUT TABLE
---------- ------- ------- --------------
TableA ColName1 NULL TableD
TableA ColName2 NULL TableE
TableA ColName3 NULL TableF
TableB ColName4 ColName1 TableG
TableB ColName5 NULL TableH
TableB ColName6 NULL TableI

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.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
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.
AarionSSQL
AarionSSQL
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 253
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.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
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.
AarionSSQL
AarionSSQL
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 253
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 ..
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
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 ?
adrian.facio
adrian.facio
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 386
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search