In some of the ETL Projects there is always a need to send the processed Flat File(CSV, XLS, TXT) to the customer through e-mail. As far as I know there is no functionality in SMTP_TO() function to add an attachment to the email notification.
We can achieve this functionality through a VB Script using the exec() function in a script.
Below steps describes the steps to achieve this functionality:
Steps: Add an attachment to BODS Job Notification email using VB Script
In order to ease the end user’s effort, it is often required that the processed Flat File(CSV, XLS etc) is sent to the user for validation. In BODS we cannot attach the report using the SMTO_TO() function
Below we will see an example of such activity.
There is no functionality in BODS to attach a file and send it to user. The same can be implemented in BODS by calling a VB Script through exec() function.
Solution: After the completion of job place a script which calls a VB Script (vbs) file to send email notification. The vbs file must be saved in the Processed location shared folder.
Declare the below Global Variable in the job.
$G_PROCESSED_LOCATION =’\\\XYZ_Location\ Processed’;
The email.vbs file has the following information;
strSMTPFrom = “User@abc.com“
strSMTPTo = “User@abc.com“
strSMTPRelay = “smtp.abc.com”
strTextBody = “JOB_NAME completed successfully in UAT. Attached is the file load status.”
strSubject = “JOB_NAME completed in UAT”
strAttachment = “\\ XYZ_Location \Processed\MyFile.xls”
Set oMessage = CreateObject(“CDO.Message”)
oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing“) = 2
oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver“) = strSMTPRelay
oMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport“) = 25
oMessage.Subject = strSubject
oMessage.From = strSMTPFrom
oMessage.To = strSMTPTo
oMessage.TextBody = strTextBody
Here is the script to send the email