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.

Making Dynamic Backup Folders In SSIS

To make dynamic backup folders from SSIS you need to do is to follow the steps.
Over all View


I am sure that you can find other ways to do this and maybe much simpler, but the concept of the blog is to give you the idea

First step is to 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...).Once adding the objects in the control flow...

First ---


Second – go to the Design Scripts and add the script setting


As you can see, the IF statement checks that if the variable is not empty, change the backup folder from the MainFolderPath to the New BackupMainFolderPath, this is when the customer wants to change the backup folder from the main root of the ETL to somewhere else. Check the Testing Scenarios.

Next step the FSYS


Testing Scenarios
Once finishing the setting run the package, you will see....

Assuming that the ETL is been running for months and you customer decided to change the backup folder, you won’t need to change anything in the package just simply add the new backup path (e.g. C:\NewBackUp) to the “uVar_BackupMainFolderPath” Variable and run the package.


In this case remember to add the package or ETL name in the variable
e.g. C:\NewBackup\MyETLname