Thursday, January 7, 2010

Emailing Error log file as attachments in SSIS

To email Error log files as an attachment please check the post “Making Dynamic Backup Folders” first

Over all View



I am sure that you can find other ways to do this; the concept is to give an idea
Step 1 -
Define few variables in the system



1. “uVar_BackupActiveFolder” when the package runs it points to a new folder each time which is the active backup folder and that is where we keep the backup and error files for that transaction for that ETL run.
2. “uVar_BackupMainFolderPath” some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details).
3. “uVar_MainFolderPath” This is where your main Folder path is basically it’s the main root of your package and its other folders (e.g. backup, Error folder, sample folder, etc...).
4. “uVar_ErrorOutPutFile” this is the path and file name of the Error file, remember each time this path and file names changes so that we can keep a Backup of each error log
5. “uVar_SMTPEmailServerName” you can enter a IP number if you like
6. “uVar_SMTPEmailTo_FromError” the list of people or email groups, use multi emails

Once adding the objects in the control flow...

Step 2 –
Adding connection Objects first
First for the Error file


Second for the SMTP object


Step 3 –
Starting from Script Task “SCR--- Make the correct path and file name for the Error Text File”
Note : The first 2 script have full details and explanation in “Making Dynamic Backup Folders


In the Design Script ...



Step 4 –
Now we have to make a object that will fail, “SCR--- Make me an Error”


Step 5 –
Now Email settingsIn the executable combo-box (drop down list) select the main root of the package, and then select OnError, drag and drop the Objects needed from the ToolBox. (See next figure)



Step 6 –
Open the DFT and make drag and drop the objects from the ToolBox.



Step 7 –
Select the “SCR---Catch Error Output description” and set...








Step 8 –
Select the Flat file destination Object and set the connection manger to “FF_DST Error output File for debugging” and ...



Step 9 –
Back to the “SMT----Send E-Mail to Notify that we have an Error in the ET section” and its settings


Testing Scenarios

Just run the package.

1 comment:

  1. I feel SSIS is a great and useful product by Microsoft and is actually loaded with great components and powerful resources to ease out and eliminate complex operations.

    SSIS Postgresql Read

    ReplyDelete