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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy