asked
SSIS Result Set and Precedence Constraint
This question is linked to another one I posted a few days ago about SSIS but it's a few steps further down the road. I have a package that depending on the result of a stored procedure it either can carry on quite happily or if the result is greater than 0 it needs to send an email to the operator and stop the SSIS package as some data needs manually checking. I've created the stored procedure and this returns a single row with 2 columns, TotalCountOfRecords and FinalOutput. If the the TotalCountOfRecords >0 then the users need to know the detail in the FinalOutput column. The TotalCountOfRecords is an Int and FinalOutput is Varchar(500). In my SSIS package I have created a SQL Task see screen dumps below: ![alt text][1] ![alt text][2] ![alt text][3] I then created an Send email object with a precedence constraint as follows: ![alt text][4] When I click on Test it says it's fine, but when I run the package it gives me the following message: ![alt text][5] and I am completely stumped, any ideas greatfully received. Many thanks [1]: /upfiles/SD5.bmp [2]: /upfiles/SD6.jpg [3]: /upfiles/SD7.jpg [4]: /upfiles/SD8.jpg [5]: /upfiles/SD9.jpg
@Mrs_Fatherjack Sorry about that. I did notice though that in the procedure that you have posted in your earlier question, you have 5 selects. All but 4th set the value of Total1 like this: set @Total1 = isnull(@Total,0) + isnull(@Count,0) when it should probably read set @Total1 = isnull(@Total1, 0) + isnull(@Count,0) instead. The 4th select has it even more different: @Total = isnull(@Total,0) + isnull(@Count,0) when it should read whatever the others do. Maybe you can follow Pavel Pawlowski suggestion, he is probably right on the money on this one, but still check the how variable is set in your proc after each select.
@Mrs_Fatherjack Is it at all possible that the @Total is null by the time the proc bails out? I mean that it is not possible to evaluate the expression to boolean if the @Total is null (SSIS might not know what to do with the result of UNKNOWN, which is nether true nor it is false).
I don't think so because I forced the @Total = 20 just to rule this out, originally I had written as: set @Total = isnull(@Total,0) which should have also stopped it being null shouldn't it?
I noticed, the the variables @Total and @Output have a scope of "Test for output parameters" which is Execute SQL Task. Then this variables will not be available on the precedence constraint. You have to define the variables on the package scope. So delete the variables. Click on the blank space of the designer and define the variables on the package scope. Then your precedence constraint should work. :-)
You are truly a genius I have battled with this for 2 days and can't believe it was so glaringly obvious. Many many thanks.
This is a common mistake and easy to do.. Mostly you overlook, that you have selected other object and then you define the variable in wrong scope.
