SSIS Conditional Logic Based On Stored Procedure Result
SSIS Conditional Logic Based On Stored Procedure Resulthttps://sqldatapartners.com/wp-content/themes/crocal/images/empty/thumbnail.jpg150150Carlos L ChaconCarlos L Chaconhttps://secure.gravatar.com/avatar/5e3365b6a313f63d0ad1fd6748a46345?s=96&d=mm&r=g
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. 🙂
Imagine what’s possible with a dedicated SQL specialist on your team.
SQL Data Partners has been acquired by Marathon Consulting, a Virginia-based Information Technology Consulting and Digital Marketing firm. We are in the process of integrating our team and services into Marathon as of May, 2023.