SSIS Conditional Logic Based On Stored Procedure Result

I was looking to implement some conditional logic in and SSIS package. In my case, I wanted the package to halt if some validation rules were not met. The logic for these validation rules are found in a stored procedure and I needed a way to return a flag to the SSIS package the success r failure of the validation. I found I could do this with the Result Set tab on an Execute SQL Task. Here is what I did to set this up.

First, I created a stored procedure with the validation rules. For simplicity, I have created a stored procedure that returns a value. You will notice I have named the returning result ‘ReturnResult’.

[sourcecode language=”sql”]
CREATE PROCEDURE mysp_ReturnAValue
AS
BEGIN
DECLARE @ReturnVariable SMALLINT
SET @ReturnVariable = 1

SELECT @ReturnVariable AS ReturnResult
END
[/sourcecode]

ConditionalLogic_ProcessOverview

Second, I created an SSIS package with a variable of type Int32 named ProcedureResults and an Execute SQL Task component. After establishing the connection on the SQL Task, I changed the ResultSet to Single Row.ConditionalLogic_ExecSQLTask I click on the Result Set ‘tab’ on the left and link the returning value from my stored procedure to the variable in the package.

ConditionalLogic_ResultSet

Third, I added two data flow tasks and linked them with a precedent constraint so the Execute SQL Task would be the first item executed. I only want a data flow tasks to execute based on the results of the stored procedure.

Fourth, I modified the Evaluation Operation of the constraint to be of type Expression and I added and expression it should use when the package runs. In the success constraint the value was @ProcedureResults > 0. ConditionalLogic_ResultSet

The failure contraint? You guess it, @ProcedureResults <= 0 .

**Interesting sidenote.  My stored procedure returns a SMALLINT.  When I created this on a client system the stored procedure returned a SMALLINT data type, just like my example above; however, the Execute SQL task was unable to process the result set.  What happened was no matter what I returned in the stored procedure, the package would stop at the Execute SQL Task and the package would succeed.  It was not until I changed my SMALLINT to an INT that it was able to process the result and continue in the package.  When I created the package on my local machine, I was unable to recreate the issue.  I haven’t been able to put my finger on the difference between the environments, but it seems to have gotten fixed somewhere.  🙂