Creating Dyanamic Email Content using SSIS

From time to time, I will be required to send emails with dynamic content to a number of users within an organization.  Typically, a user will want data in that email that is relevant to them or relevant to their subordinates for reporting/notification purposes.  Thankfully, SSIS can handle this task fairly easily.

The scenario in this post requires that I reference a time reporting tool and send an email reminder to all users who have not entered the required number of hours (40) into the tool for the current week.  Since the time reporting tool does not have any email addresses, I will reference a separate table using a lookup transformation that will provide me user email addresses.

First, drag and drop a data flow task on to the control flow pane.  After that, add an OLEDB source and choose the database that you wish to extract data from and ‘SQL command’ as your data access mode.

01

Here is the code for the above query:

select [user] , Sum(hours) AS TotalHours
from dbo.Timeless
where [DATE] < DATEADD(day, -1, getdate()) AND
      [DATE] > DATEADD(day, -8, getdate())
group by [user]
having Sum(hours) < 40

Once that is complete, add a ‘LookUp transformation’ to your data flow task.  Here, you will reference the table that holds the email address for all users.

02

If you look at the image below, you will see that I am joining the ‘user’ field in the Timeless table to the ‘USERID’ field in the EMAIL_XREF table to get the ‘EMAIL’ (or email address) into my data flow.

03

Once you have completed your LookUp transformation, you will need to add a ‘Recordset Destination’ to your dataflow.  A recordset destination does not save data to an external data source. Instead, the Recordset destination saves data in memory in a recordset that is stored in an Integration Services package variable of the Object data type.  Here, I have created this record set with a variable name of ‘User::EmailList’ which will be referenced later.

04

Under the ‘Input Columns’ tab in your record set destination editor, you will want to select the ‘user’, ‘TotalHours’ & ‘EMAIL’ fields to be stored in your record set destination.  Notice the order in which these fields are stored:

0: user

1: TotalHours

2: EMAIL

05

This is what your dataflow task should look like once you have completed the above steps.  I have added an OLEDB Destination to store any records where a match was not found in the LookUp.

06

Once you have completed your data flow task, connect a For Each Loop to the data flow task.  Under the ‘collection’ tab, choose ‘Foreach ADO Enumerator’ and choose the ‘User::EmailList’ as the ADO object source variable.

07

Under the variable mappings tab, select the 3 user variables that were mapped into your RecordSet destination and specifiy the index (how the recordset was sorted; reference above) of each of the variables.

08

Inside of the ForEach loop, drag and drop a SendMail task to send an email for each record in the recordset.  Set your ‘From’ field to a default email and specifiy the subject for the email. (all other fields will be read from the recordset)

09

Under the expressions tab, choose ‘MessageSource’ and ‘ToLine’

10

The expression editor text for the above expressions:

MessageSource: “Our records indicate that user ” + @[User::UserName1] +  ” has only entered ” + @[User::TotalHours] + ” hours for this week.  Please complete your timecard entries before the end of business today.

Thank you.”

ToLine: @[User::eMailAddress]

This will create a custom and dynamic message that emails useres who have entered less than 40 hours for that week.  The email content specifies the number of total hours each user has entered and sends it to that specific user at the end of the week.  This should help to remind users who have not completed their ‘timecard’ and it should help to keep them out of trouble with upper management!

11

Advertisements

Tags: , , , ,

One Response to “Creating Dyanamic Email Content using SSIS”

  1. Keerthivasan M Says:

    Simple and Excellent resource for novice developers!
    Good Job! :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: