Learn MSBI Part 11:- Send Mail Task & Script Task Component

Links to other Labs of Learn MSBI Step by Step.

Learn MSBI Part 1:- Creating SQL tables, Practicals of SSIS-ETL(Extraction, Transformation & Loading)

Learn MSBI Part 2:- Performing Validation on Records

Learn MSBI Part 3:- SCD (Slow Changing Data)

Learn MSBI Part 4:- Fact and Dimension.

Learn MSBI Part 5:- CUBE Creation

Learn MSBI Part 6:- Displaying reports using SSRS

Learn MSBI Part 7:- DQS (Data Quality Services)

Learn MSBI Part 8:- Data Profiling Task

Learn MSBI Part 9:- Pivot, UnPivot and Aggregation

Learn MSBI Part 10:- Execute SQL Task component (SSIS)

Learn MSBI Part 12:- CDC Concept using SSIS

Learn MSBI Part 13:- Asynchronous, Synchronous, Full-blocking, Semi-blocking & No-blocking

Learn MSBI Part 14:- Understanding Page Split and maintaining SSIS Performance

Learn MSBI Part 15:- Debugging in deployment – Instrumentation using Data Taps

Learn MSBI Part 16:- Using Multi-threading and its performance in SSIS program

Send Mail Task Component

Send Mail Task is a readymade component given in SSIS available under Common –> SSIS Toolbox for specialized for sending email only. There are also other Task using that options by email can be send like using Execute SQL Task and Script Task by writing customized programming language C# or VB code within Visual Studio.

In this lab will first start with readymade mailing Send Mail Task component will also go into explaining configuring each field details. It simply uses SMTP (Simple Mail Transfer Protocol) and generally used to communicate success or failure of process task or some results of the package which should be known by the user or may be attaching some attachment then sending the mail so on and get the running job done.

In order to use Send Mail Task component simply drag and drop it on Control Flow from SSIS Toolbox available under Common as shown in the image below.

Once component is on the Control Flow next is to configure it for that do right click on the component or double click the click the component similar way which was used in previous labs to configure the component.

Once the Editor is open then click on the General tab, give suitable name to the task and add description to it for the ease of understanding.

After the setting General tab go and click on following tab called Mail in order to configure it. Under the Mail tab click on SmtpConnection à “<New connection…>” which will open new window of SMTP Connection Manager Editor over here

1) Provide Name and

2) Description to the connection manager

3) Add SMTP server name here for learning purpose SMTP server à mail.learnmsbistepbystep.com is defined.

4) Next is do tick on the check box to enable Windows Authentication. So by enabling this it will authenticate the current logged in user’s identity i.e. it will confirm user is valid or not. In our SSIS case it means that user is the one who is executing the package. Here SMTP server will search for credentials i.e. User ID and Password as windows authentication. Once the credentials are found it will validated with credentials saved in the ADS(Active Directive Services) after match found to be correct SMTP will then allow user to send email.

5) Then click on OK to close connection manager editor window.

Once Connection Manager is configured then click on the Mail tab if that tab is not enabled.

Then fill in the remaining fields by providing From, To, Subject Line, Email matter and add attachments if any present.

And then click on OK to close Send Mail Task editor window.

After executing the package it failed due SMTP was not able to authenticate user as SMTP do not provide to enter custom user and only logged in windows authenticated user can send mail.

Limitation of Send Mail Task

• Send Mail Task do not provide any provision to set custom user id & password due to which it remains limited to send emails.
• It is also do not have option to specify custom port no. to send email.
• It can only send plain text i.e. formatted mails cannot be send using Send Mail Task and SMTP connection manager.

Send Mail Task is only meant for sending mails internally and cannot be sent widely. Internally it is used to send mails to know the execution status of the package.

Script Task:

In this lab will see the working of Script Task component which will help to overcome the limitation of Send Mail Task where custom can be set, custom port no. can be set and even formatted email can be send.

For that first drag and drop the Script Task component on the Control Flow. Once the component is on the Control Flow do right click or double click the component to configure it and click on Edit to configure it.

After the Script Task Editor is open click on the Script tab available on the left side and then click on “Edit Script” button on the right side bottom as shown in the below image.

It will open Visual Studio to Edit the script a where customized manual coding can be done in C# language as shown in the image down below: –

Step 1: – Under the namespace make two new entries with using statement: –

using System.Net.Mail;

using System.Net;

Step 2: – Write code to configure SMTP Client by creating object of “SmtpClient” and then declare the Host name, Port no. and enable SSL to send and receive email.

Step 3: – Provide the custom credentials which will be used to send and receive mails.

Step 4: – In the step configure the “FromAddress” i.e. from the email address using which mail will be send.

Step 5: – In this step set the “ToAddress“ to the one who will receive mail and set formatted mail matter.

Step 6: – Write code to send mail.

Once the script is edited then click on “saveall” option available on the top and close the Visual Studio window and click on OK to close the Script Editor window.

Now execute the package which will show component status running successfully with green symbol at the top.

Next to go and check the set “ToAddress” mail ID whether it has received the mail.

It shows the mail receiving successfully with correct mail matter with set formatting to the text with red colour and appearing the back ground colour to be in grey shade.

So from above lab it was able to send mail using custom credentials through Script Task component made possible.

Also get to see following video on MSBI project series which start from the basic to start SSIS : -