Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Variable mapping - For Each Loop Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:51 AM
Points: 16, Visits: 215
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.
Post #1337959
Posted Tuesday, July 31, 2012 11:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
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.
Post #1338058
Posted Tuesday, July 31, 2012 12:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:51 AM
Points: 16, Visits: 215
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.
Post #1338097
Posted Tuesday, July 31, 2012 3:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
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.
Post #1338213
Posted Wednesday, August 01, 2012 3:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:51 AM
Points: 16, Visits: 215
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 ..
Post #1338386
Posted Wednesday, August 01, 2012 6:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
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 ?
Post #1338455
Posted Thursday, August 02, 2012 5:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:43 PM
Points: 335, Visits: 367
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


Post #1339576
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse