July 31, 2012 at 8:57 am
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.
July 31, 2012 at 11:15 am
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.
July 31, 2012 at 12:36 pm
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.
July 31, 2012 at 3:32 pm
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.
August 1, 2012 at 3:11 am
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 ..
August 1, 2012 at 6:17 am
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 ?
August 2, 2012 at 5:16 pm
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