Script task

The script task is a multi-purpose tool that we can use in a package to fill almost any requirement that is not may be the task included with integrated services. The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. You use the Script task for work that must be done once in a package.

Example

Ø  Detecting an Empty Flat File with the Script Task

Ø  Gathering a List for the Foreach Loop with the Script Task

Ø  Querying the Active Directory with the Script Task

Ø  Monitoring Performance Counters with the Script Task

Ø  Working with Images with the Script Task

Ø  Finding Installed Printers with the Script Task

Ø  Sending an HTML Mail Message with the Script Task

Ø  Working with Excel Files with the Script Task

Ø  Sending to a Remote Private Message Queue with the Script Task

Ø  Using Variables in the Script Task

Ø  Connecting to Data Sources in the Script Task

Ø  Raising Events in the Script Task

Ø  Logging in the Script Task

Here I am going to use script task to send the mail.

Double click on script task and you will get script task editor. Here I am going to hard coded value but can also pass the value through variables.

try             {                 MailMessage mail = new MailMessage();                 SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");                 mail.From = new MailAddress(“XXXXXXXX@gmail.com");                 mail.To.Add("XXXXXXXXXXX@gmail.com");                 mail.Subject = "Test Mail";                 mail.Body = "This is for testing SMTP mail from GMAIL using SSIS Script task";                 SmtpServer.Port = 587;                 SmtpServer.Credentials = new System.Net.NetworkCredential("XXXXXXXXXX@gmail.com", "****(Your gmail password)***");                 SmtpServer.EnableSsl = true;                 SmtpServer.Send(mail);                 MessageBox.Show("mail Send");             }             catch (Exception ex)             {                 MessageBox.Show(ex.ToString());             }

Save and close the script task editor. Execute the package.