September 5, 2013 at 6:31 pm
I'm looking to create an SSIS package that sends me an email if certain dates aren't where they are supposed to be within our 2008 database.
After nightly system processing, 7 date fields need to be "todays" date and 2 of the date fields need to be "yesterdays" date. If all 7 don't equal "todays" date OR the other 2 fields don't equal "yesterdays" date, I want the SSIS package to send me an email.
Me getting an email would be sufficient (I know how to create that part of the package) but if I could get an email with the dates, that would be even better.
I'm not looking for a step by step suggestion but rather a suggestion on how I'd start this type of a package to see if I could take it from there....such as what control flow items I'd need to start out with. I already have the script to pull the dates, just not sure where to take it from there.
TIA,
John
September 6, 2013 at 12:42 am
Hi, you can create a SQL Task with the script to identify the dates condition, resulting in a flag, put the result set in a variable and use a constraint option before the send mail task evaluating the flag variable.
Let me know!
September 9, 2013 at 10:20 am
J M-314995 (9/5/2013)
After nightly system processing, 7 date fields need to be "todays" date and 2 of the date fields need to be "yesterdays" date. If all 7 don't equal "todays" date OR the other 2 fields don't equal "yesterdays" date, I want the SSIS package to send me an email.
You could also read the dates into variables and then set an expression in a precedence constraint to test the condition you described and send the email if the conditions aren't matching the requirements. [Edit to add task information]. Read the dates into variables using an 'Execute SQL Task'.
September 9, 2013 at 3:47 pm
Thank you both for your suggestions. I most truly appreciate the feedback.
I now have a working SSIS package.
Here's what I did in a nutshell for anyone that may find this later...
1. Used Execute SQL Task and Send Mail Task control flow items.
2. Since I was grabbing one row of dates, I configured my connection, inserted the SQL script and set ResultSet to 'Single row'. I created one more column per result. I put the logic to if the date is current date on 7 of the 9 fields, I set the corresponding column to 0. On the other 2 columns that need to be yesterdays date I set to 0 if it matched...kinda like if GETDATE() - 1 then 0 ELSE 1. If the dates didn't match the condition, I set the column equal to 1.
3. Not sure if this was needed (I'm thinking it is), configured the on the Execute SQL Task control item Result Set where I matched the value within the script to a pre-configured variable name.
4. Created an expression to where if the second corresponding columns sum up to greater than zero, it triggers the send email control item.
5. Since I wanted the email to contain the dates from the SQL script, I used the expression builder to bring in the date variables within the Send Mail Task. Under Property, I used MessageSource.
Thanks again,
John
September 9, 2013 at 3:55 pm
Glad to hear you got it working. Thanks for reporting back your approach.
September 10, 2013 at 1:02 am
Glad that it worked!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply