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’.
CREATE PROCEDURE mysp_ReturnAValue
DECLARE @ReturnVariable SMALLINT
SET @ReturnVariable = 1
SELECT @ReturnVariable AS ReturnResult
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. 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.
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.
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. 🙂