Send Mail in SSIS using GMail

Send Mail in SSIS using GMail:

Hi Friends, I was going through the send mail task in SSIS. It was very much similar to ODI. There no such facility to use authenticated smtp server. So we have to take the help of script to send mail like we were doing in ODI. The only difference is that in ODI, we were using script written in java and here in SSIS it must in VB or C#. Lets take a look in below steps in order to setup the send mail task.

Create 7 variables as show in below screenshot. Variable name can be anything. Its up to you.

ssissendgmail1

ssissendgmail2

ssissendgmail3

 

Drag the script task editor into the package/control flow and double click on it. You can see below window.

ssissendgmail4

 

select the variables that you have created for the send mail task and click on Ok.

ssissendgmail5

Its time to use the script. Click on edit script.

below codes need to be used in the script file. Copy paste and change the variable name if you have taken something different name than mine. Then save the script file and close it.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Net.Mail;

namespace ST_81efe477883549f0bf922ca8e3d9943e.csproj
{
[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
string sSubject = “SSIS Test Mail Using Gmail Credentials”;
string sBody = “Great!!! Test Message Sent Through SSIS. Thank You”;
int iPriority = 2;

if (SendMail(sSubject, sBody, iPriority))
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
//Fails the Task
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

public bool SendMail(string sSubject, string sMessage, int iPriority)
{
try
{
string sEmailServer = Dts.Variables[“EServer”].Value.ToString();
string sEmailPort = Dts.Variables[“EPort”].Value.ToString();
string sEmailUser = Dts.Variables[“EUser”].Value.ToString();
string sEmailPassword = Dts.Variables[“EPassword”].Value.ToString();
string sEmailSendTo = Dts.Variables[“ETo”].Value.ToString();
//string sEmailSendCC = Dts.Variables[“sEmailSendCC”].Value.ToString();
string sEmailSendFrom = Dts.Variables[“EFrom”].Value.ToString();
string sEmailSendFromName = Dts.Variables[“EFromName”].Value.ToString();

SmtpClient smtpClient = new SmtpClient();
MailMessage message = new MailMessage();

MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);

//You can have multiple emails separated by ;
string[] sEmailTo = Regex.Split(sEmailSendTo, “;”);
//string[] sEmailCC = Regex.Split(sEmailSendCC, “;”);
int sEmailServerSMTP = int.Parse(sEmailPort);

smtpClient.Host = sEmailServer;
smtpClient.Port = sEmailServerSMTP;
smtpClient.EnableSsl = true;

System.Net.NetworkCredential myCredentials =
new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
smtpClient.Credentials = myCredentials;

message.From = fromAddress;

if (sEmailTo != null)
{
for (int i = 0; i < sEmailTo.Length; ++i)
{
if (sEmailTo[i] != null && sEmailTo[i] != “”)
{
message.To.Add(sEmailTo[i]);
}
}
}
/*
if (sEmailCC != null)
{
for (int i = 0; i < sEmailCC.Length; ++i)
{
if (sEmailCC[i] != null && sEmailCC[i] != “”)
{
message.To.Add(sEmailCC[i]);
}
}
}*/

switch (iPriority)
{
case 1:
message.Priority = MailPriority.High;
break;
case 3:
message.Priority = MailPriority.Low;
break;
default:
message.Priority = MailPriority.Normal;
break;
}
Attachment myAttachment = new Attachment(“D:/FF/test.txt”);
message.Attachments.Add(myAttachment);
message.Subject = sSubject;
message.IsBodyHtml = true;
message.Body = sMessage;

smtpClient.Send(message);
return true;
}
catch (Exception exc)
{
return false;
}
}
}
}

ssissendgmail6

 

Now right click on the script task editor and execute it.

ssissendgmail7

ssissendgmail8

Thats all. Dont forget to check your inbox 😀 ( Thats the obvious thing to test whether its working or not 🙂 )
Let me know hows everything going with you. Thank You.

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

16 Comments

  1. Hi,
    Tried this code and gives me an error.
    Error: The script returned a failure result.
    Can you please help me out?
    Regards.

  2. Hi Bhabani,

    After building the code I get a single error below in relation to this line of the C# code. Do you know why?

    [System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]

    Error 1 The type or namespace name ‘AddIn’ does not exist in the namespace ‘System’ (are you missing an assembly reference?) C:\Users\anthonyc\AppData\Local\Temp\4\Vsta\SSIS_ST110\Vsta3pi5Uz1OCk6YpbaTjtq7Xw\Vsta5__zj9AsA50__yMIw__HvKztQ\ScriptMain.cs 10 9 ST_462b2f3771884b0facff916185041060

    Thanks,
    Anthony.

  3. Hi All,
    I just followed same thing as stated above. The only error message I can see is “Error: The script returned a failure result”. And there’s no error in the script.
    Kindly need help on this.
    Thanks in advance.

    Regards,
    iya

  4. Hi all,

    Had the same error and figured out why. the Script Task uses an attachment. If the attachment doesn’t exist then it fails.

    Attachment line:

    Attachment myAttachment = new Attachment(“F:/FF/test.txt”);

    I created a test folder “C:/FF” and notepad file “test.txt”. Updated patch to (“C:/FF/test.txt”);

    and it worked and sent the email.

    Cheers.

  5. This is not the best method having that the user and password of the SMTP Server are exposed.

    What I do is to add a SQL Task and the use the sp_send_mail stored procedure. This method require to have the Database Mail configured at SQL Server. I’m sending Excel files generated by SSIS using this.

    Example:

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘ITSupport’,
    @recipients = ‘john.doe@email.com’,
    @subject = ‘Testing attachements from SQL Server’,
    @file_attachments = ‘c:\MyDocuments\foo.txt’

    Let me know how it works for you.

    Regards,

  6. its not working , its saying mail sending fail , and no error in script ,everything configured correctly ,can you help me in this to work it .

  7. I am receiving the following Package Validation Error:
    Error at Strip Task: The binary code for the script is not found. Please open the script in designer by clicking Edit Script button an make sure it builds successfully. Error at Script Task: There were errors during task validation.

    Any input you could provide would be appreciated

  8. Hi. Thanks to this! it is working fine now. For those having “Error: The script returned a failure result”, Just comment these codes below

    Attachment myAttachment = new Attachment(“D:/FF/test.txt”);
    message.Attachments.Add(myAttachment);

    unless you would’nt want any attachment on your mail. Try it, it works on me.

  9. Hi,

    After executing the package, i’m getting below message –
    Error: The script returned a failure result.

    Although, no specific error is getting shown.And in last just getting message as

    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Could some please suggest what’s going wrong.

  10. If the above package is failing then, please follow below steps.

    1. Go to https://myaccount.google.com/->Security->Signing in to Google->App passwords
    Google will ask for your account password to re-authenticate you.
    2. Please enter your Gmail password and proceed.
    3. Select App->Mail and Device->Windows Computer
    4. Click on GENERATE button
    5. 16 digit password will be generated and use it in your package.

    Hope this will help you to solve your problem.

Leave a Reply

Your email address will not be published.


*