question

Mrs_Fatherjack avatar image
Mrs_Fatherjack 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
ssisconstraintresultprecedence
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
1 Like 1 ·
@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).
0 Likes 0 ·
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?
0 Likes 0 ·
Many thanks, I will do both.
0 Likes 0 ·

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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. :-)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.