SSIS Package Problem - Send Mail Task Sends Mail When It Shouldnt Based On Precedence Constraints Setup
I have a simple package that inserts values to a table daily to keep track of constistent counts on uploaded documents, then checks the latest values to see if they do not match, if not then the step issues a 'true' else it issues 'false'. I set a precedence constraint to check for 'true' and if so go to the next step to issue an email. Problem is the second step is issuing a 'false' value so the precedence constraint shouldn't send an email. The problem seems to be the second step in that the package doesn't see the value. Here's the sql I used in that step: IF EXISTS ( select * from [tririgadw].[dbo].[timesheet_upld] Where EXISTS(select top 1 * from [tririgadw].[dbo].[timesheet_upld] WHERE upload_cnt <> created_cnt and CAST( FLOOR( CAST( run_date AS float) ) AS smalldatetime) = CAST( FLOOR( CAST( GETDATE() AS float) ) AS smalldatetime) ORDER BY run_date) ) SELECT 'True' as count ELSE SELECT 'False' as count ---I'm checking the two columns and making sure it's the most recent date by comparing against the system date. If it finds that the two columns don't match then it outputs 'True' otherwise it returns a 'False' value. This step works in SSMS and provides the value I expect in test. The next step is an email task which is preceded by a precedent constraint using expression that equals True. I thought I had the logic correct, guess not.
February 24th, 2011 8:05pm
Is your Execute SQL step configured to send its output to a result set? You will need a boolean varialbe to hold the output. What is the Expression in the Prec.Constraint editor?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
February 24th, 2011 8:10pm
Wait. correction -- it does output to a result set of either 'true' or 'false'. The constraint options are setup in the following manner: Evaluation Operation: Expression Expression: True Logical AND. All Constraints must evaluate to true. This is using a package variable called 'count' that matches the output column for the result set.
February 24th, 2011 8:26pm
I changed the count variable to boolean for the package setting True as the value. Ran the package again and got this: Error: Failed to lock variable "IF EXISTS ( select * from [tririgadw].[dbo].[timesheet_upld] Where EXISTS(select top 1 * from [tririgadw].[dbo].[timesheet_upld] WHERE upload_cnt <> created_cnt and CAST( FLOOR( CAST( run_date AS float) ) AS smalldatetime) = CAST( FLOOR( CAST( GETDATE() AS float) ) AS smalldatetime) ORDER BY run_date) ) SELECT 'True' as count ELSE SELECT 'False' as count" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
February 25th, 2011 5:32am
Todd, Thanks for the advice. I'll give it a try.
February 25th, 2011 1:15pm
First bit of advice: Name your variables better. If I came across a variable named "Count" I would expect an integer value, not boolean o9r text. I would go fvor something like "FileExists" or "RecordsExist" or "IsSomething". Makes your package easier to understand by the next guy. Next: Set your SQL Task to have a Singe Result Set, then on the Result Set page, add a reference to your "RecordsExist" variable, and make sure you name the result set as "0". (Result Set column are 0 based names, kind of like input variables, atleast for OLE DB Connections. In the PRecedence Constraint to the Send Mail Task, edit it to "Expression and Constraint". In the Expression, pu in the following: @RecordsExist (If this is a TRUE Boolean variable, that's all you need) If your variable is of tyep String and may contain either "true" or "false" then put in: @RecordsExist == "true" If it's an integer (like maybe you modify the SQL to return the actual Row Count) then put in the following: @RowCount > 0 Hint: Use a breakpoint on the PreExecute event of the Send Mail task and then get into the Locals window when execution 'breaks' so you can inspect the variable's value. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
February 25th, 2011 1:41pm