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

passing variables in a Foreach loop container Expand / Collapse
Author
Message
Posted Wednesday, June 4, 2014 6:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 89, Visits: 290
Hi all,

I hope this is the right forum to post, else please let me know.

I'm not an expert with SSIS but I have to maintain and extend some packages and am stuck with following problem: I need to run a stored procedure on a table, created earlier in the package, for each "client" in that table.

What the stored procedure does, is select informations according to the client number and output it in a text file. The stored procedure call is EXEC SP101 'LIVE','Client1' then 'Client2', ...

The part of the SSIS package that does this is
1. <<Execute SQL task >> that selects all the distinct clients and puts the result in a system.object
2. <<For each container>> with link to the collection retrieved above
1. <<Execute SQL task >> that executes the SP EXEC SP101 'LIVE',? with parameter mapping to the client id, but not sure I did it the right way.

My problem is that, although the SP is run a correct number of times, no data are generated. It seems the clientID is never passed to the SP.

If you need any further information, please let me know.

Any help will be much appreciated.

Thanks a lot
Post #1577304
Posted Wednesday, June 4, 2014 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 13,538, Visits: 10,412
In the for each loop container, you need to map the current value to a variable.
This variable is then used in the second Execute SQL Task.

You can put a breakpoint on the execute sql task and inspect the value of the variable before it is executed.

If the parameter mapping with the question mark is troublesome, you could create another variable, SQLQuery, and put an expression on that variable.
For example:

"EXEC SP101 'LIVE','" + @[User::myVariable] + "' "

In the Execute SQL Task, you use this variable as the source for the SQL statement.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577309
Posted Wednesday, June 4, 2014 6:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 89, Visits: 290
Thanks Koen, I'll give it a try and let you know ...
Post #1577310
Posted Wednesday, June 4, 2014 7:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 89, Visits: 290
So, I got a bit further ...

1. using the Watch, I can confirm that the variable holds the correct clientID at each loop

2. tried to replace the "?" with @[User::clientID]
but got following error: Execute SQL Task: Executing the query ""exec SP101 'LIVE','" + @[User::Cl..." failed with the following error: "Incorrect syntax near '+'.".

3. last I wanted to use this variable as the source for the SQL statement in the Execute SQL Task, as you suggested, but unfortunatly I can't figure out how to do it


Still trying to find it out, but thanks a lot if you can help me ...

Post #1577332
Posted Wednesday, June 4, 2014 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 13,538, Visits: 10,412
rot-717018 (6/4/2014)
So, I got a bit further ...

1. using the Watch, I can confirm that the variable holds the correct clientID at each loop

2. tried to replace the "?" with @[User::clientID]
but got following error: Execute SQL Task: Executing the query ""exec SP101 'LIVE','" + @[User::Cl..." failed with the following error: "Incorrect syntax near '+'.".

3. last I wanted to use this variable as the source for the SQL statement in the Execute SQL Task, as you suggested, but unfortunatly I can't figure out how to do it


Still trying to find it out, but thanks a lot if you can help me ...



You can't put the expression directly in the Execute SQL Task.
You need to put it in a variable, and then use that variable in the task.

Execute SQL Task Editor (General Page)

--> use SQLSourceType as Variable




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577337
Posted Wednesday, June 4, 2014 8:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 89, Visits: 290
ok, I understood what you meant and have put the SQL query in a variable executed in the Execute DQL Task.

Unfortunatly still the same error. It seems it does not replace @[User::ClientID] with the ID it retrieves from the loop container.

still searching .......
Post #1577374
Posted Wednesday, June 4, 2014 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 13,538, Visits: 10,412
Is the variable property EvaluateAsExpression set to true?
Put a breakpoint on the preexecute event of the Execute SQL Task and check the value of the SQL query in the locals window.

Last option: use profiler to see what statement is sent to the database.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577536
Posted Thursday, June 5, 2014 9:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 89, Visits: 290
Evaluateasexpression is set to true

ran the profiler and @[User::ClientID] is not evaluated.

I'll look back into it on tuesday.

anyway, thanks for your help koen, I'll keep you informed ...
Post #1577865
Posted Thursday, June 5, 2014 11:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 13,538, Visits: 10,412
Maybe post some screenshots Tuesday, so I can see what is going on.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse