February 6, 2015 at 12:51 pm
I'm working on a project for a client where SSIS handles automated daily imports of numerous Excel / CSV / other files, performs some transactional activities on each of those files, and outputs info. from each file into new Excel files followed a standardized schema.
The client has requested to be notified automatically via SSIS via email if, with any input file, the date in the input file's "Rehire Date" field is greater than the date in the input file's "Hire Date" field. So, if a file has 80 records and 3 records in the file meet the above criteria of the person's Rehire Date > Hire Date, an email goes out giving some basic info. on those 3 records (i.e. Person's Name, SSN, Rehire Date, Hire Date, a another field or two of info.).
I know how to send emails within SSIS, so that's not an issue.
I'm trying to figure out the proper task / approach to use in SSIS to capture (in the example above) those 3 records & fire-off the email.
I've thought through this a bit and am drawing a blank as to how to best accomplish this.
Any suggestions are much appreciated. Thank you.
February 6, 2015 at 4:48 pm
brad.mccollum (2/6/2015)
I'm working on a project for a client where SSIS handles automated daily imports of numerous Excel / CSV / other files, performs some transactional activities on each of those files, and outputs info. from each file into new Excel files followed a standardized schema.The client has requested to be notified automatically via SSIS via email if, with any input file, the date in the input file's "Rehire Date" field is greater than the date in the input file's "Hire Date" field. So, if a file has 80 records and 3 records in the file meet the above criteria of the person's Rehire Date > Hire Date, an email goes out giving some basic info. on those 3 records (i.e. Person's Name, SSN, Rehire Date, Hire Date, a another field or two of info.).
I know how to send emails within SSIS, so that's not an issue.
I'm trying to figure out the proper task / approach to use in SSIS to capture (in the example above) those 3 records & fire-off the email.
I've thought through this a bit and am drawing a blank as to how to best accomplish this.
Any suggestions are much appreciated. Thank you.
I've done something similar in the past, sending ETL load parameters as part of a success/failure email.
My recommended approach would be something like this:
1. Stage your data.
2. Use an "execute t-sql" task to query and return the "bad" records from your staging table(s).
3. Insert the results of the query into an object variable.
4. Use a script task and recordset objects within the task to extract the data, and generate the text for the email.
5. Because of the way you extract the data here, I would also recommend using the same script task to send the email.
I've used a similar method to generate some html code for the body of a status email...works pretty well.
Hope this helps.
February 7, 2015 at 1:53 pm
Thank u 4 the recommendations. I'll try your suggestions, or I'd possibly come up with the idea of using a conditional transformation to get those records meeting the Rehire Date > Hire Date criteria. One or the other should work for me. Really appreciate the input!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply