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 of OUTPUT
If you wanted to use the stored procedure within a tool, SSMS, .NET, whatever, it'd look something like
DECLARE @orderCount int = 0;
EXECUTE dbo.TestStoredProcSSVariable @orderCount OUTPUT;
SELECT @orderCount As OrderCountVariable;
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 ? like 0
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
SELECT @OrderCount AS OrderCount;
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,
- Right click on the project and select Add new Biml file
- replace the contents of the file with the following XML
- Fix the values in line 5. Update the
Data Source to a real server and Provider to align with your SSIS version. Looking at your screenshot, this will likely be SQLNCLI10.1
- Right click on BimlScript.biml and choose Generate SSIS Packages
Bimlscript.biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection
Name="tempdb"
ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
/>
</Connections>
<Packages>
<Package
Name="dba_114775"
ConstraintMode="Linear"
>
<Tasks>
<ExecuteSQL
ConnectionName="tempdb"
Name="SQL Make procedure">
<DirectInput>
<![CDATA[IF EXISTS
(
SELECT
*
FROM
sys.procedures AS P
INNER JOIN
sys.schemas AS S
ON S.schema_id = P.schema_id
WHERE
S.name = 'dbo'
AND P.name = 'TestStoredProcSSVariable'
)
BEGIN
DROP PROCEDURE dbo.TestStoredProcSSVariable
END
GO
CREATE PROCEDURE dbo.TestStoredProcSSVariable
(
@OrderCount int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
SET @OrderCount = 1135;
SELECT @OrderCount AS OrderCount;
END
GO
]]>
</DirectInput>
</ExecuteSQL>
<Container Name="SEQC Result set" ConstraintMode="Linear">
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Make procedure.Output"></Input>
</Inputs>
</PrecedenceConstraints>
<Tasks>
<ExecuteSQL
ConnectionName="tempdb"
ResultSet="SingleRow"
Name="SQL SingleRow">
<DirectInput>EXECUTE dbo.TestStoredProcSSVariable ?;</DirectInput>
<Results>
<Result VariableName="User.orderCountb" Name="0" />
</Results>
<Parameters>
<Parameter DataType="Int32" VariableName="User.orderCountb" Name="0" />
</Parameters>
</ExecuteSQL>
<ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
<DirectInput>SELECT 1;</DirectInput>
</ExecuteSQL>
<ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
<DirectInput>SELECT 1;</DirectInput>
<PrecedenceConstraints>
<Inputs>
<Input
OutputPathName="SQL Placeholder.Output"
EvaluationOperation="ExpressionAndConstraint"
EvaluationValue="Success"
Expression="@[orderCount] < 5" />
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
</Tasks>
</Container>
<Container Name="SEQC Output Parameter" ConstraintMode="Linear">
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL Make procedure.Output"></Input>
</Inputs>
</PrecedenceConstraints>
<Tasks>
<ExecuteSQL
ConnectionName="tempdb"
Name="SQL Output parameter">
<DirectInput>EXECUTE dbo.TestStoredProcSSVariable ? OUTPUT;</DirectInput>
<Parameters>
<Parameter
DataType="Int32"
VariableName="User.orderCount"
Name="0"
Direction="Output" />
</Parameters>
</ExecuteSQL>
<ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
<DirectInput>SELECT 1;</DirectInput>
</ExecuteSQL>
<ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
<DirectInput>SELECT 1;</DirectInput>
<PrecedenceConstraints>
<Inputs>
<Input
OutputPathName="SQL Placeholder.Output"
EvaluationOperation="ExpressionAndConstraint"
EvaluationValue="Success"
Expression="@[orderCount] < 5" />
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
</Tasks>
</Container>
<ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
<DirectInput>SELECT 1;</DirectInput>
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SEQC Result set.Output" />
<Input OutputPathName="SEQC Output Parameter.Output" />
</Inputs>
</PrecedenceConstraints>
</ExecuteSQL>
</Tasks>
<Variables>
<Variable DataType="Int32" Name="orderCount">-1</Variable>
<Variable DataType="Int32" Name="orderCountb">-1</Variable>
</Variables>
</Package>
</Packages>
</Biml>
Enjoy the following SSIS package
@[User::orderCount]Result Setpage. simple-talk.com/sql/ssis/…@[User::orderCount]<5?(dt_bool)1:(dt_bool)0. Don't forget to post results back to this thread if sucessful. GL!