March 6, 2014 at 8:55 am
John Mitchell-245523 (3/6/2014)
You're making things too complicated. Your result set contains only one row, so change the result set to No Result Set and use output parameters in the Parameter Mapping page instead. Then you can use the variables in your next Execute SQL task without the need for a For Each Loop container.John
Hi John. I am not sure what you mean by result set contains only one row. The query resuls returned by the 1st Execute SQL Task may return 1 to 100 items with commas added to be used in the IN statement for the 2nd Execute (update) SQL Task. I figured the Foreach would be used to concatenate the result set and save it as a string in the variable.
March 6, 2014 at 9:13 am
Let me check that I understand. Your first task contains a query like
SELECT ConcatenatedList
FROM SourceTable
from which the result set is
[font="Courier New"]ConcatenatedList
----------------
1,2,3,4,5,6,7,8[/font]
Your second task then uses that result set thus:
UPDATE DestinationTable
SET Something = 'Something'
WHERE SomethingElse IN (1,2,3,4,5,6,7,8)
If I've got that right, then the best thing to do is as I suggested. (Parameter placeholder may be different depending on which kind of connection manager you use.)
[font="Arial Narrow"]SELECT ? = ConcatenatedList
FROM SourceTable[/font]
and set the ResultSet property to No Result Set. In the Parameters page, output to a variable called CList. For the next task, do away with the loop container and have just the Execute SQL task. Use an expression to set the query - something like this:
[font="Arial Narrow"]"-- **** Expression ****
UPDATE DestinationTable
SET Something = 'Something'
WHERE SomethingElse IN (" + @[User::CList] + ")"[/font]
Does that make sense? It's done from memory, so if I've got any syntax, property names etc wrong, I apologise.
John
March 6, 2014 at 9:35 am
In the 1st Execute SQL Task, the resultset is something like this:
Item
----
"Item1",
"Item2",
"Item3",
"Item4"
My version of SQL Server does not allow for the Concatenate function, so I figured I need to loop through the result set with the Foreach to build the string variable (like StringBuilder in C#) for the IN statement.
WHEELS
March 6, 2014 at 9:41 am
In that case, I think I wouid use a Data Flow to dump that result set into a staging table, and then use a MERGE or UPDATE statement in an Execute SQL task to do the processing. It's simpler, and more importantly, it processes the result as a set instead of one row at a time.
John
March 6, 2014 at 10:12 am
Thank you. I am on vacation this week, but 1st thing on Monday, I will try to implement.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply