December 18, 2009 at 1:03 pm
Hi:
I have an SSIS package that (among other things) compares TableA and TableB and if a record's expiration date in TableA doesn't match the same record's expiration date in TableB, it copies the record from TableA into TableX and deletes it from TableA. A GETDATE() value is inserted into TableX's ErrorDate field with every record so I know when each discrepancy occurred (this doesn't happen often--maybe once a month).
I'd like to add a step to my SSIS package to alert me by email if there are any records in TableX with the current date in the ErrorDate field so I can investigate the discrepancy. I'm assuming I should use the Send Mail feature, but I'm not sure how to specify that it should notify me if there are more than 0 records in TableX with the current date in ErrorDate. Can anyone help?
Thanks!
December 21, 2009 at 1:01 pm
I would break this up into several tasks. The first would do all the comparisons and inserts into Table X using whatever logic you need.
The next task would be a SQL Script task that would run a query to get the count on Table X and assign the result to a variable.
The next task would be an email task. It would be connected on success with the SQL Script task. Then edit the workflow connection between the two tasks and add an expression for when that variable has a value > 0.
...hope this helps.
December 21, 2009 at 2:49 pm
Ted Zatopek (12/21/2009)
I would break this up into several tasks. The first would do all the comparisons and inserts into Table X using whatever logic you need.The next task would be a SQL Script task that would run a query to get the count on Table X and assign the result to a variable.
The next task would be an email task. It would be connected on success with the SQL Script task. Then edit the workflow connection between the two tasks and add an expression for when that variable has a value > 0.
...hope this helps.
I'd do just about the same thing minus the script task. Why use a script task to run a SELECT COUNT(*) query? Use the Execute SQL Task to run your query and place the results into a package variable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply