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 pest 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.

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

16 comments

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

Prasanna, Any solution for the above error ?

Sorry for the delay. Can you copy pest the complete error ? You might have problem with single quote and double quote.

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.

Georgian Pirvu / Reply

make sure that you are

using System.Text.RegularExpressions;
using System.Net.Mail;

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

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.

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,

Indeed it is Carlos!!
Thanks for your feedback and suggestion.
To the readers: You can refer below link for more details.
http://msdn.microsoft.com/en-IN/library/ms190307.aspx

I was having trouble getting SSIS 2013 to load the script it kept erroring. The problem was that Microsoft has made a change to the System.AddIn.AddIn. This site (http://www.varigence.com/Forums?threadID=1330) helped me correct the issue. I hope this helps someone else who maybe experience issues.

BTW thanks for this post!

can you please create simple package not having any attachment and variables,

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 .

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

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.

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.

try below link. it worked just perfect for me… dont forget to replace the paramters highlighted with your actual parameters.

http://etlscrapbook.blogspot.in/2017/02/send-mail-from-gmail-smtp-server-by.html?m=1

Leave a replay to scottc Cancel reply

required*

Are you a human? *