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.