Emailing work log notification to individual users using SSIS

Emailing work log notification to individual users by using SSIS
Another project that I had worked on was to send emails with attached excel work log files to individual users; I used the same package with some modification to send excel Management MIS reports to CEOs & managers, the Excel file contained pie charts and etc...

Criteria
1. A sample Excel (97 – 2003) file with one sheet named “Report”
2. In the “Report” sheet the field names that need to be reported to the user must be on the first row, e.g.

3. The list of the fields will be used in the SSIS package; in this post I will be only using one field (UserID).
4. The Excel file name must be renamed to “SampleExcelFile.xls”
5. Make a main root folder e.g. “C:\SSIS\Email to list with attached Excel File”
6. Make two other sub folders 1- “Sample” 2- “ToBeEmailed”
7. Copy the Excel Sample File in the “Sample” folder

Note(s)
1. The attached file format will be in *.xls format (Excel (97 – 2003))

Over all View

Defining some variables in the package
One of the most important part of a SSIS package are the variables, because some variables will be defined in the SSIS Configuration File(s) and/or Table(s), so that the package can be used dynamically.
Example: The server name on your computer is different that the server name on production server so basically you can have the “server name variable” defined in the SSIS configuration XML file or table so that it can be changes when deploying the package from one server to another.



1. “uVar_BackupActiveFolder” Each time the package runs it makes a backup folder known as the current backup folder which will be used to save the Excel backup files and/or maybe the Error text file.
2. “uVar_BackupMainFolderPath” Some customers prefer to have the backup folder in another server so basically we allow them to redirect the folders location by using this variable. If this variable is empty it will make a default backup folder name (See backup scripts).
3. “uVar_BackupUserFolder” once the variable “uVar_BackupActiveFolder” is defined, the package will make subfolders with the name of the users, so that it will be easier to trace what was reported on that day and for whom.
4. “uVar_DestinationFileNameRenamed” To move the excel file to the backup folder we need to rename it.
5. “uVar_ListOfUsers” A SSIS variable to save the list of users that needs to be notified.
6. “uVar_MainFolderPath” This is the path where you will be having your main ETL folder root; it can contain the Package, configuration files, backup, sample folder, ToBeProcessed folder and etc... (C:\SSIS\ETLName)
7. “uVar_Source_SQLQueryString” A SQL statement that provides the work log of each user, The TOP statement is been used for design time only, the script string will change in the package loop (check object=> “SCR--- Make the WHERE condition of the Source table”).
8. “uVar_SourceSampleExcelFile” the location of the Excel sample file.
9. “uVar_ToBeEmailedFile” The file that will be emailed individually
10. “uVar_ToBeEmailedFolder” The folder that has the file that needs to be emailed.
11. “uVar_UserEmail” The email of the current user in the Loop.
12. “uVar_UserID” The current user ID in the Loop.
13. “uVar_UserName” The current user name in the Loop.

Defining Connection Manager
I am going to define only one connection in this example I am sure you can set the rest of them yourself.

1. Excel connection manager



Set the “DelayValidation” to TRUE and enter the right the connection string e.g.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SSIS\Email to list with attached Excel File\Sample\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES";

2. SMTP Connection Manager

3. SQLServerSourceConnection

You can add the Error handling features in the package.

Adding Object (controls) in the Control Flow
Now we can add the objects as seen in the “Over all View”
1. SCR---- Making the backup folder string from BackupFolderName Variable (Dynamic Backup folder yyyy-mm-dd-hh-mm-ss)


Redirect the backup folder in the “uVar_BackupMainFolderPath” variable; if the variable is empty the system will pick up the default folder name “BackUpFolder” in the main folder path of “uVar_MainFolderPath”. E.g.
C:\SSIS\Email ..................Excel File\BackUpFolder\2010-04\2010-04-27-103108.



2. FSYS----Creating the backup folder from BackupFolderName
Making the backup active folder from the “uVar_BackupActiveFolder” variable. C:\SSIS\Email ..................Excel File\BackUpFolder\2010-04\2010-04-27-103108.

3. FSYS----Creating the ToBeEmailed Folder
Creating the folder. C:\SSIS\Email ..................Excel File\ ToBeEmailed.

4. SCR--- Make ToBeEmailedFile String "Folder and File Name"
A “variable pointer” that points to the sample excel file.

5. DFT --- Find the list of users to Email to
We now need to get the list of users that need to be notified about their daily work list report, mainly we need the fields of the UserID, UserName and email, the number of fields and the order of the fields are very important (See: Foreach Loop Container).
The DFT looks like...

Retrieving the list of users from the OLE DB Source object.

Mapping fields.

Saving the list of users in a SSIS variable object, the loop in the package will be looping through this variable user by user

6. Foreach Loop Container
What the Loop section dose, is to loop though the object variable “uVar_ListOfUsers” (that has the list of users that needs to be notified) user by user, make an Excel file for each user, make a backup folders and move the excel file to the right backup folder for each user and finally email to the user with the right excel file attached.
Loop settings...

Now we can see why the order and the number of fields in the SELECT statement are critical because of the fields mapping in the loop.

7. FSYS -- Copy a Template Empty Excel file to the ToBeEmailed Folder
In each loop the package (for each user in the notification list) copies a sample of the Excel sample file (SampleExcelFile.xls) to the “uVar_ToBeEmailedFolder”, the file must have a sheet named “Report”.

8. SCR--- Make the WHERE condition of the Source table

Now we have to make a query string that has the current user work log only, in other words the excel file that will be emailed to each user must have his/her work list report only, so we must filter the report table user by user by using the UserID.

Note: In this select statement you must have all the field names that will be in the excel file as header fields, in my sample I will be only using one field (UserID)
The string will be saved in the “uVar_Source_SQLQueryString” variable that will be used by the “OLE DB Source” that is in the “DFT --- Main Task”.

9. DFT --- Main Task
Saving the work log list for the current user into the excel file.

The OLE Source is pointing to the WorkList table that is been filtered by the current UserID.
In the variable “uVar_Source_SQLQueryString” I had defined a query like “SELECT TOP 1 UserID .....” and I am sure that raised few questions like, why using TOP? Why hard coding a UserID? The answers are in the next step, while developing a package you need to define the fields and you need to be able to map them in the OLE DB source editor, and that’s why.

Saving the work list to the excel file in the “Report” sheet.

10. SCR--- Make Folderstring Like "Backup Folder and UserID"

C:\SSIS\Email .........File \BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1

11. FSYS---- Make Backup folder in the name of the User
C:\SSIS\Email .........File \BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1

12. SCR--- Rename file names with the format of "yyyy-mm-dd-hhmmssff"

The string will look like
C:\SSIS\Email ...... File\BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1\FName1 LName1 2010-04-27-10311137.xls

13. FSYS---- Moving processed files to the "Backup Active Folder"

Moving the file from ....
C:\SSIS\Email ..........File\ToBeEmailed\ SampleExcelFile.xls
To .....
C:\SSIS\Email ...... File\BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1\FName1 LName1 2010-04-27-10311137.xls

14. Send Mail Task

Testing Scenarios
Before testing make sure that you have set the variables according to the snap shot of the variables that I have and check the “Criteria”.
Once you run the package you should be able to see.