Really need help w/ what should be simple question!

Hello,

Basically, all I am trying to do is create an SSIS pkg that will read a list of stored procedures from a table. I set up an EXECUTE SQL TASK to do this. I am saving the result set to a variable (type object).

Next, I call a FOREACHLOOP container, which references the variable from the EXECUTE SQL TASK.

But I'm not sure what to do next? I just want the FOR EACH LOOP to simply execute the stored procedure name as given by the first EXECUTE SQL TASK.

I was thinking to put another EXECUTE SQL TASK inside the FOR EACH LOOP, but I don't know how to reference the stored procedure name correctly, or if this is even allowable.

I am not a scripter :-)

Can you please offer me some advice on what to do next? I am desperately seeking a solution.

Thank you!!!

In the Variable Mappings tab of your For Each container, make sure that you are mapping the necessary columns (like the stored proc name) of your resultset to variables.

Create a new variable to hold the call to the stored procedure. In the properties for the variable, make sure EvaluateAsExpression is true. In the Expression property, construct the string for a call to the stored proc, using the variables you mapped in the For Each container. Add an Execute SQL task in your For Each container, and set the SQLSourceType to variable. Then pick the variable you created as the SourceVariable.

If you wait a bit, I'll post an example on my blog.

|||

Example:

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

The sample package is attached to the post.

Hope this helps.

|||

Hi John,

I looked *everywhere*, and I honestly don't see where to set "EvaluateAsExpression" is true. That is, where in the world do I set the properties for the variable in the FOREACH containter editor? I really don't see this anywhere.

I also looked at your example from your post, and I don't see this anywhere, either?

Thanks

|||

Hi John,

I examined your example very carefully. However, I don't know what you mean by the statement:

There is a variable named "select". This variable uses an expression to build the following select statement: "SELECT ID, ParentID, ChildName FROM ChildTable WHERE ParentID = " + the "parentID" variable. The "select" variable is then used in another Execute SQL task.

Where are you building the expression? I don't see it.

Thanks

|||Select the variable in the variables window (not in the container editor) and hit F4. This should bring up the properties for the variable. EvaluateAsExpression is an option in that window. This is also where the expression is defined.|||

Are you talking about the DataObjectVariable under EnumeratorProperties in the Properties of the ForEachLoop container?

I see my the name of my ADO Object Source Variable (User::Reports) , but not the mapped column (User::report_name) > which contains the name of the stored procedure.

But hitting F4 does not bring up anything.

?

Thanks

|||

I think I may be getting "closer", in spite of things probably not being set correctly at this point.

So far I have an EXEC SQL task >> FOREACHLOOP >> EXEC SQL task.

And it appears it is actually trying to RUN the stored procedure names. But I am getting this error. Do you think this has to do with my not setting the EvaluateAsExpression? Or something else? Thanks!

SSIS package "Migration - Run Report Comparison.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "MY_STORED_PROC" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Migration - Run Report Comparison: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

|||No, I'm talking about the window where you originally define the variable. On the menu bar for Visual Studio, choose SSIS..Variables, and you should see a list of variables defined in your package. You may need to click the "Show All Variables" button, as some variables may be scoped to specific items in the package.|||

Yes, I see it now, thanks so much for explaining this. I am very new to SSIS and packages in general.

So I set the variable which contains the stored procedure name EvaluateAsExpression as True.

However, I am still getting aforementioned error.

|||

Ha!

Figured it out. The devil is in the details as they say!

What was missing was that in the Parameter Mapping tab of the 2nd EXEC SQL TASK was that I needed to set the Parameter Name to 0.

I still don't fully understand the meaning of all the 0's, although I think it has something do with how OLEDB does things.

Anyhow, it's working... now onto step 2 - write outputs of sp's to name files.

Thanks again

Your example was very useful!!

|||

Could you mark the question as answered? Makes it easier for others to find answers for similar questions.

Thanks.