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


passing variables in a Foreach loop container


passing variables in a Foreach loop container

Author
Message
rot-717018
rot-717018
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 403
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
:-)
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16560 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rot-717018
rot-717018
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 403
Thanks Koen, I'll give it a try and let you know ...
rot-717018
rot-717018
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 403
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 ...
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16560 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rot-717018
rot-717018
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 403
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 .......
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16560 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rot-717018
rot-717018
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 403
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 ...
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16560 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
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