I am trying to get the value from a stored procedure into an SSIS variable, and then testing to see if two SSIS tasks can work if I added an expression. So for an example, I am trying to use this stored procedure:
Maybe I am even setting up the SSIS variable properties entirely wrong, because I am also not sure if I am doing this the right way for the stored proc value to be imported into an SSIS variable. Please do tell me if you need any more screencaps of anything else.
Here is the task example:
And here is Precedence Constraint Editor screencap:
And here is the properties for the first task:
I want it to go forward (or fail) based on that condition. But when I test it, the process flows from first task to second regardless, and only shows me "100% complete" for the first task and nothing about whether it checked this expression to be true or not. How can I do such a thing and what is going wrong?
I do have a variable in SSIS called 'orderCount' to get the value from stored proc.
Best Answer
You have two choices to make this work. Either you can use a Single Result Set or you can use the OUTPUT parameter. You're currently using neither correctly.
OUTPUT parameter
Your stored procedure is defined as having a parameter of
@OrderCount
with a direction ofOUTPUT
If you wanted to use the stored procedure within a tool, SSMS, .NET, whatever, it'd look something like
It is valid to run the above without specifying
OUTPUT
but look at the value of @orderCount. It changes from 1435 to 0.The same holds true when you're using the Execute SQL Task within SSIS. You must specify that the parameter is on OUTPUT and also specify it in the Parameter mappings tab.
Also specify the variable you want to map and use the OUTPUT direction there. Here I've mapped the result into an SSIS Variable of type Int32 called
orderCount
Single Result Set
You have the first part of this correct - you've specified that the result set is Single Row.
You'll note that I am using
EXECUTE dbo.TestStoredProcSSVariable ?
as you must specify an input value or the proc call will break (at least as you've defined it). You could have hard coded a value in lieu of the?
like0
Then, on the Result Set tab, here I'm mapping up the first column (zeroth ordinal) to a Variable called
orderCountb
If you run the provided stored procedure, you will not get a value in orderCountb. Why? Because you aren't returning anything from the stored procedure call. I added a final statement inside the stored procedure of
Do it yourself
You can explore either approach using the following biml. What is biml? The Business Intelligence Markup Language is the operating system for BI. Why you care about it is that it will allow you to transform some XML into an SSIS package. All you need to do is download and installed the free addon BIDS Helper
After installing BIDS Helper,
Data Source
to a real server andProvider
to align with your SSIS version. Looking at your screenshot, this will likely be SQLNCLI10.1Bimlscript.biml
Enjoy the following SSIS package