HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Assiging stored procedure results to SSIS variable

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlssissql-serverstackoverflowdba
storedssisassigingprocedureresultsvariable

Problem

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.

Solution

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


    
        
    
    
        
            
                
                    
                        
                    

                
                
                    
                        
                            
                        
                    
                    
                        
                            EXECUTE dbo.TestStoredProcSSVariable ?;
                            
                                
                            
                            
                                
                            
                        
                        
                            SELECT 1;
                        
                        
                            SELECT 1;
                            
                                
                                    
                                
                            
                        
                    
                
                
                    
                        
                            
                        
                    
                    
                        
                            EXECUTE dbo.TestStoredProcSSVariable ? OUTPUT;
                            
                                
                            
                        
                        
                            SELECT 1;
                        
                        
                            SELECT 1;
                            
                                
                                    
                                
                            
                        

                    
                
                
                    SELECT 1;
                    
                        
                            
                            
                        
                    
                
            
            
                -1
                -1


Enjoy the following SSIS package

Code Snippets

DECLARE @orderCount int = 0;
EXECUTE dbo.TestStoredProcSSVariable @orderCount OUTPUT;
SELECT @orderCount As OrderCountVariable;
SELECT @OrderCount AS OrderCount;
<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] &lt; 5" />
                                </Inputs>

Context

StackExchange Database Administrators Q#114775, answer score: 10

Revisions (0)

No revisions yet.