Case
In SQL 2016 CTP 2.3 Microsoft introduced a new simple way with to get the name of the column causing the error with some .NET code in a Script Component. In the final release this code doesn't work.

Solution
Not sure why, but they changed the code. Instead of one line we now need two lines. Below the complete example with the new code.


1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".

Add Script Component Transformation

3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.

Input Columns

4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.

Output Columns

5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following three lines of code to it.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    // componentMetaData (starting with a lowercase "c") is just a name.
    // You can change that name if you like, but also change it in the
    // second row.
    IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    ' componentMetaData (starting with a lowercase "c") Is just a name.
    ' You can change that name if you Like, but also change it in the
    ' second row.
    Dim componentMetaData As IDTSComponentMetaData130 = TryCast(Me.ComponentMetaData, IDTSComponentMetaData130)
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn)
End Sub

6) Testing
Close the VSTA environment to save the code and press OK the close editor. Now add a Data Viewer behind the Script Component Transformation to see the results

Error Description and ColumnName

Case
I want to execute a PowerShell Script within an SSIS package, but there is no PowerShell task. How do I do that in SSIS?

Solution
There are 3 possible solutions within SSIS:

1. Using a Custom Task
2. Using the Execute Process Task
3. Using the Script Task

For this example I will use a simple PowerShell script that exports a list of installed hotfixes on the server to a CSV file, but the possibilities with PowerShell are endless.

#PowerShell: c:\temp\hotfixes.ps1
[CmdletBinding()]
Param(
    # FilePathCsv parameter is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$FilePathCsv
)

# Create folder if it doesn't exists
$FolderPath = split-path $FilePathCsv
# Check if folder exists
if (-Not (Test-Path $FolderPath))
{
 Write-Host "Creating folder $FolderPath"
 New-Item -ItemType directory -Path $FolderPath
}

# Export list of hotfixes to CSV file
Get-WmiObject -Class Win32_QuickFixEngineering -ComputerName .| Export-Csv $FilePathCsv

The script has a parameter to supply a csv filepath. It checks whether the folder mentioned in this path exists. If not it creates it. Then it exports the data to specified CSV file.

A) Using a Custom Task
You could look for a custom task like the one on codeplex (also see this example), but it seems not to be an active project. And so far I haven't found any commercial PowerShell Tasks for SSIS.

B) Using the Execute Process Task
To execute the PowerShell script with an Execute Process Task you need a command that looks like:
powershell.exe -command "c:\temp\hotfixes.ps1 -FilePathCsv c:\temp\export\hotfixes.csv"

Add an Execute Process Task to the Control Flow and give it a suitable name like "EPR - Create hotfixes CSV". Edit it and go to the Process page. In the Excutable property you add the complete path of the PowerShell executable. For example: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
And in the Arguments property you add the rest of the command: -command "c:\temp\hotfixes.ps1 -FilePathCsv c:\temp\export\hotfixes.csv"

PowerShell via Execute Process Task

This step is optional but with two SSIS string variables (or parameters) containing the paths and an expression on the arguments property you can make it a little more flexible: "-command \"" + @[User::PathPowerShellScript] + " -FilePathCsv " + @[User::PathHotfixes] + "\""

Process Task with variables(expressions) in the arguments

When you run this in Visual Studio you will see a PowerShell window for a couple of seconds. With the StandardOutputVariable and StandardErrorVariable you can catch the output of the PowerShell script into an SSIS variable.

PowerShell window when executing the Execute Process Task

C) Using the Script Task
If you don't like the Execute Process Task then you could use the Script Task to execute a PowerShell script, but to use PowerShell in .NET you need to install the Windows Software Development Kit (SDK) for Windows. It contains the assembly System.Management.Automation.dll which we need to reference in our Script Task later on. Don't forget to install it on all your machines (DTAP: Development, Test, Acceptance and Production).

Choose Windows Software Development Kit for the libraries

First add two string variables (or parameters) to your package. PathHotfixes containts the filepath of the to be created CSV file and PathPowerShellScript contains the filepath of the PowerShell file.

String variables

Add the Script Task to the Control Flow and give it a suitable name like "SCR - Create hotfixes CSV". Then edit it choose the Script Language (C# or VB.NET).

Choose ScriptLanguage C# or VB

Then add the two string variables (or parameters) as Read Only Variables. We are using them to avoid hardcoded paths in our script.

ReadOnlyVariables

After that hit the Edit Script button to open the VSTA environment. In the Solution Explorer (upper right corner) right click the References and choose Add Reference... then browse to C:\Program Files (x86)\Reference Assemblies\Microsoft\WindowsPowerShell\3.0 and select the System.Management.Automation.dll file.

Add Reference

In the code we need to add extra namespaces (usings in C# and imports VB) to shorten the code. You can either add these rows to the existing namespaces by adding them in the region Namespaces. Or you can add a separate region for custom namespaces.

#region Custom Namespaces
using System.Management.Automation;
using System.Management.Automation.Runspaces;
#endregion

or VB.NET

#Region "Custom Namespaces"
Imports System.Management.Automation
Imports System.Management.Automation.Runspaces
#End Region

Next go to the main method and add the following code

// C# code
/// 

/// This method executes a PowerShell file with a parameter
/// 
public void Main()
{
    // Create a new Runspace to run your command in
    RunspaceConfiguration runspaceConfiguration = RunspaceConfiguration.Create();
    Runspace runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration);
    runspace.Open();

    // Create a new command with a parameter
    Command command = new Command(Dts.Variables["User::PathPowerShellScript"].Value.ToString());
    CommandParameter commandParameter = new CommandParameter("FilePathCsv", Dts.Variables["User::PathHotfixes"].Value.ToString());
    command.Parameters.Add(commandParameter);

    // Execute the PowerShell script in the Runspace
    Pipeline pipeline = runspace.CreatePipeline();
    pipeline.Commands.Add(command);
    pipeline.Invoke();

    // Close the Script Task and return Success
    Dts.TaskResult = (int)ScriptResults.Success;
}

or VB.NET

' VB.NET code
' This method executes a PowerShell file with a parameter
Public Sub Main()
 ' Create a new Runspace to run your command in
 Dim runspaceConfiguration As RunspaceConfiguration = RunspaceConfiguration.Create()
 Dim runspace As Runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration)
 runspace.Open()

 ' Create a new command with a parameter
 Dim command As New Command(Dts.Variables("User::PathPowerShellScript").Value.ToString())
 Dim commandParameter As New CommandParameter("FilePathCsv", Dts.Variables("User::PathHotfixes").Value.ToString())
 command.Parameters.Add(commandParameter)

 ' Execute the PowerShell script in the Runspace
 Dim pipeline As Pipeline = runspace.CreatePipeline()
 pipeline.Commands.Add(command)
 pipeline.Invoke()

 ' Close the Script Task and return Success
 Dts.TaskResult = ScriptResults.Success
End Sub

Alternative: If you don't want to store the PowerShell code in a separate file because it makes it harder to deploy it through the DTAP environment then you can also add the PowerShell code directly in your .NET code or store it in an SSIS variable and pass that to the Script Task. In that case the code slightly changes.

// C# code
/// 

/// This method executes a PowerShell script
/// 
public void Main()
{
    // Create a new Runspace to run your script in
    RunspaceConfiguration runspaceConfiguration = RunspaceConfiguration.Create();
    Runspace runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration);
    runspace.Open();

    // Create string with PowerShell code (or get it from an SSIS variable)
    string PowerShellScript = @"$FilePathCsv = ""XXX""

                                # Create folder if it doesn't exists
                                $FolderPath = split-path $FilePathCsv
                                # Check if folder exists
                                if (-Not (Test-Path $FolderPath))
                                {
                                 Write-Host ""Creating folder $FolderPath""
                                 New-Item -ItemType directory -Path $FolderPath
                                }

                                # Export list of hotfixes to CSV file
                                Get-WmiObject -Class Win32_QuickFixEngineering -ComputerName .| Export-Csv $FilePathCsv";

    // Replace the hardcode dummy path with a value from an SSIS variable
    string exportFile = Dts.Variables["User::PathHotfixes"].Value.ToString();
    PowerShellScript = PowerShellScript.Replace("XXX", exportFile);

    // Execute the PowerShell script in the Runspace
    Pipeline pipeline = runspace.CreatePipeline();
    pipeline.Commands.AddScript(PowerShellScript);
    pipeline.Invoke();

    // Close the Script Task and return Success
    Dts.TaskResult = (int)ScriptResults.Success;
}

or VB.NET (less readable due the lack of a good string continuation on multiple lines in VB)

' VB.NET code
' This method executes a PowerShell script
Public Sub Main()
 ' Create a new Runspace to run your script in
 Dim runspaceConfiguration As RunspaceConfiguration = RunspaceConfiguration.Create()
 Dim runspace As Runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration)
 runspace.Open()

 ' Create string with PowerShell code (or get it from an SSIS variable)
 Dim PowerShellScript As String = "$FilePathCsv = ""XXX""" & Environment.NewLine &
          "# Create folder if it doesn't exists" & Environment.NewLine &
          "$FolderPath = split-path $FilePathCsv" & Environment.NewLine &
          "# Check if folder exists" & Environment.NewLine &
          "if (-Not (Test-Path $FolderPath))" & Environment.NewLine &
          "{" & Environment.NewLine &
          " Write-Host ""Creating folder $FolderPath""" & Environment.NewLine &
          " New-Item -ItemType directory -Path $FolderPath" & Environment.NewLine &
          "}" & Environment.NewLine &
          "# Export list of hotfixes to CSV file" & Environment.NewLine &
          "Get-WmiObject -Class Win32_QuickFixEngineering -ComputerName .| Export-Csv $FilePathCsv"

 ' Replace the hardcode dummy path with a value from an SSIS variable
 Dim exportFile As String = Dts.Variables("User::PathHotfixes").Value.ToString()
 PowerShellScript = PowerShellScript.Replace("XXX", exportFile)

 ' Execute the PowerShell script in the Runspace
 Dim pipeline As Pipeline = runspace.CreatePipeline()
 pipeline.Commands.AddScript(PowerShellScript)
 pipeline.Invoke()

 ' Close the Script Task and return Success
 Dts.TaskResult = ScriptResults.Success
End Sub

After that close the VSTA editor and the Script Task Editor and test the Script Task. If it was successful you can use a Data Flow Task to read the CSV file. With this relative easy code and this custom task example you could create your own custom PowerShell task.

Case
There is an upload and download task in the SSIS Azure Pack, but how can I delete a storage container in my Azure strorage account that was created with SSIS?

Solution
At the moment there is no Azure File System Task for SSIS, but you can also do this with a Script Task.

1) Azure SDK
First download and install the Azure SDK for .NET 2.7 (or newer). This SDK contains an assembly that we need to reference in our Script Task. When you hit the download button you can download multiple files. The one you need is called MicrosoftAzureLibsForNet-x64.msi (you can't install both 64 and 32bit).

Libraries only is enough

2) SSIS Feature Pack for Microsoft Azure
Download and install (next, next, finish) the SSIS Feature Pack for Microsoft Azure (2012, 2014).

SSIS Azure Feature Pack

3 Package Parameters
Unfortunately we cannot use the Azure Storage Connection Manager because the properties we need are sensitive (writeonly in a Script Task), therefore we will use two string package parameters. The first one contains the name of the container that you want to delete and is called "ContainerName". You can find the exact name in the Azure management portal.

Container in Storage Account

The second package parameter is a sensitive string parameter named "ConnectionStringStorageAccount". It contains the connection string of the Azure Storage Account. The format should be like this (you have to replace the red parts):
DefaultEndpointsProtocol=https;AccountName=ssisjoost;AccountKey=34PQgq+Kpr9Mz4rUfGoTpR1GZrGcC/SaFphXt3aUmgzXrcowtba0vz+uq1bIYBS5FkFYEaJ6W2CYVSsB5C8AEDQ==

The first red part of the string is the name of the storage account. You can look it up on the Azure management portal.

Storage Account "ssisjoost"

The second red part is the Account Access Key which can also be copied from Azure.

Storage Account Access Keys

The end result should look like this. Of course you can use different names or project parameters instead, but then you have to change that in the Script Task!

Package Parameters

4) Add Script Task
Add a Script Task to the Control Flow and give it a suitable name like "SCR - Delete Storage Container". Edit it, choose the ScriptLanguage and select the two string parameters from the previous step as ReadOnlyVariables. Then click on the Edit Script button to open the VSTA environment.

Edit Script Task

5) Add reference
In the solution explorer we first need to add a reference to one of the assemblies installed in step 1: Microsoft.Windows.Storage.dll which is located in the folder: C:\Program Files\Microsoft SDKs\Azure\.NET SDK\v2.7\ToolsRef\

Adding a reference in C#

6) The code - Import custom namespaces
To shorten the code we need to add some usings (C#) or some imports (VB). Add these just below the standard imports or usings.

// C# Code
#region CustomNamespaces
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;
#endregion

or VB.NET code

' VB.NET Code
#Region "CustomImports"
Imports Microsoft.WindowsAzure
Imports Microsoft.WindowsAzure.Storage
Imports Microsoft.WindowsAzure.Storage.Auth
Imports Microsoft.WindowsAzure.Storage.Blob
#End Region

7) The code Main method
In the main method we need to replace the existing comments and code with the following code.

// C# Code
public void Main()
{
    // Get parameter values. Notice the difference between
    // a normal and a sensitive parameter to get its value
    string connStr = Dts.Variables["$Package::ConnectionStringStorageAccount"].GetSensitiveValue().ToString();
    string containerName = Dts.Variables["$Package::ContainerName"].Value.ToString();

    try
    {
        // Retrieve storage account from connection string.
        CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connStr);

        // Create the blob client.
        CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

        // Create a reference to the container you want to delete
        CloudBlobContainer container = blobClient.GetContainerReference(containerName);

        // Delete the container if it exists
        container.DeleteIfExists();

        // Show success in log
        bool fireAgain = true;
        Dts.Events.FireInformation(0, "Delete Storage Container", "Container " + containerName + " was deleted successfully", string.Empty, 0, ref fireAgain);

        // Close Script Task with Success
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        // Show Failure in log
        Dts.Events.FireError(0, "Delete Storage Container", ex.Message, string.Empty, 0);

        // Close Script Task with Failure
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

or VB.NET code

' VB.NET Code
Public Sub Main()
    ' Get parameter values. Notice the difference between
    ' a normal and a sensitive parameter to get its value
    Dim connStr As String = Dts.Variables("$Package::ConnectionStringStorageAccount").GetSensitiveValue().ToString()
    Dim containerName As String = Dts.Variables("$Package::ContainerName").Value.ToString()

    Try
        ' Retrieve storage account from connection string.
        Dim storageAccount As CloudStorageAccount = CloudStorageAccount.Parse(connStr)

        ' Create the blob client.
        Dim blobClient As CloudBlobClient = storageAccount.CreateCloudBlobClient()

        ' Create a reference to the container you want to delete
        Dim container As CloudBlobContainer = blobClient.GetContainerReference(containerName)

        ' Delete the container if it exists
        container.DeleteIfExists()

        ' Show success in log
        Dim fireAgain As Boolean = True
        Dts.Events.FireInformation(0, "Delete Storage Container", "Container " + containerName + " was deleted successfully", String.Empty, 0, fireAgain)

        ' Close Script Task with Success
        Dts.TaskResult = ScriptResults.Success
    Catch ex As Exception
        ' Show Failure in log
        Dts.Events.FireError(0, "Delete Storage Container", ex.Message, String.Empty, 0)

        ' Close Script Task with Failure
        Dts.TaskResult = ScriptResults.Failure
    End Try
End Sub

Case
Before SSIS 2016 there was no way to get the name of the column that caused the error. Atleast not without a custom transformation or a very complicated script that looped through a copy of your package to get all details. Now there is a simple solution available.

Solution
There was already a script available to get the error description. Now you can use a similar way to get the columnname. First download SSDT 2015 (SSDT and SSDT-BI have been merged!).

* UPDATE: code below outdated / changed click here for new code *

1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".

Add Script Component Transformation

3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.

Input Columns

4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.

Output Columns

5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following two lines of code.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
}

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    Row.ErrorColumnName = Me.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn)
End Sub

6) Testing
Now add a Data Viewer behind the Script Component Transformation to see the results

Error Description and ColumnName

It's not ready yet, but I'm proud to announce the first SSIS book by me and fellow MVP Régis Baccaro (B|T).

Extending SSIS with .NET Scripting

Extending SSIS with .NET Scripting will be a timeless and comprehensive scripting toolkit for SQL Server Integration Services to solve a wide array of everyday problems that SSIS developers encounter. The detailed explanation of the Script Task and Script Component foundations will help you to develop your own scripting solutions, but this book will also show a broad arsenal of readymade and well documented scripting solutions for all common problems.

Feel free to contact us for ideas and suggestions. We will post status updates on twitter and our blogs.

It could be that the number of blogposts will slightly reduce the coming months due writing obligations (but only temporarily).

Case
I want to send mail within SSIS, preferably HTML formatted. What are the options?

Solutions

There are a couple of solutions to mail within SSIS:

1. Send Mail Task
2. Script Task with SmtpClient Class
3. Execute SQL Task with sp_send_dbmail
4. Custom Tasks like Send HTML Mail Task or COZYROC

To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.

Add these four variables to your package

B) Script Task
The Script Task is a little harder to use than the Send Mail Task (if you don't have .Net knowledge), but it doesn't have the same drawbacks as the Send Mail Task. It uses the SmtpClient Class and it has an HTML formatted mail option and a configurable port number.

1) SMTP Connection Manager
This first step is optional. I like to use as much as possible standard connection managers. Right click in the Connection Managers window and add an SMTP Connect manager. Add the SMTP server and change other options if necessary (other options are not used in this example). The alternative is to use an extra SSIS string variable for storing the SMTP Server.

SMTP Connection manager

2) Add Script Task
Add a Script Task to your Control Flow (or one of the event handlers). Give it a suitable name and add the SSIS variables as readonly variables to the Script Task.

Add all SSIS variables as ReadOnly

3) The Script
In the Scipt I'm using the variables and the connection manager to fill the properties of the SMTP client. Copy the contents of the Main method to your method and add the extra import/using on top.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;          // Added

namespace ST_df6618207373422d961b80ca8b6a56e2
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Storing SSIS variables in .Net variables. You could skip this step and call the SSIS variables in the actual mail code
            // to reduce the number of code lines. Or you could fill these .Net variables with hardcoded values.
            String SendMailFrom = Dts.Variables["SendMailFrom"].Value.ToString();
            String SendMailTo = Dts.Variables["SendMailTo"].Value.ToString();
            String SendMailSubject = Dts.Variables["SendMailSubject"].Value.ToString();
            String SendMailBody = Dts.Variables["SendMailBody"].Value.ToString();

            // Get SMTP Server from SMTP Connection Manager. Alternative is to use extra variables or paramters instead:
            // String SmtpServer = Dts.Variables["SmtpServer"].Value.ToString();
            String SmtpServer = Dts.Connections["My SMTP Connection Manager"].Properties["SmtpServer"].GetValue(Dts.Connections["My SMTP Connection Manager"]).ToString();

            // Create an email and change the format to HTML
            MailMessage myHtmlFormattedMail = new MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody);
            myHtmlFormattedMail.IsBodyHtml = true;

            // Create a SMTP client to send the email
            SmtpClient mySmtpClient = new SmtpClient(SmtpServer);
            mySmtpClient.Port = 2525; // If you want to use a different portnumber instead of the default. Else remove this line.
            mySmtpClient.Send(myHtmlFormattedMail);

            // Close Script Task with success
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

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

or use VB.Net code

' VB.Net Code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail             ' Added

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        ' Storing SSIS variables in .Net variables. You could skip this step and call the SSIS variables in the actual mail code
        ' to reduce the number of code lines. Or you could fill these .Net variables with hardcoded values.
        Dim SendMailFrom As String = Dts.Variables("SendMailFrom").Value.ToString()
        Dim SendMailTo As String = Dts.Variables("SendMailTo").Value.ToString()
        Dim SendMailSubject As String = Dts.Variables("SendMailSubject").Value.ToString()
        Dim SendMailBody As String = Dts.Variables("SendMailBody").Value.ToString()

        ' Get SMTP Server from SMTP Connection Manager. Alternative is to use extra variables or paramters instead:
        ' Dim SmtpServer as String = Dts.Variables("SmtpServer").Value.ToString();
        Dim SmtpServer As String = Dts.Connections("My SMTP Connection Manager").Properties("SmtpServer").GetValue(Dts.Connections("My SMTP Connection Manager")).ToString()

        ' Create an email and change the format to HTML
        Dim myHtmlFormattedMail As New MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody)
        myHtmlFormattedMail.IsBodyHtml = True

        ' Create a SMTP client to send the email
        Dim mySmtpClient As New SmtpClient(SmtpServer)
        mySmtpClient.Port = 2525 ' If you want to use a different portnumber instead of the default. Else remove this line.
        mySmtpClient.Send(myHtmlFormattedMail)

        ' Close Script Task with success

        Dts.TaskResult = ScriptResults.Success
    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

4) The result
Close the Script Task and execute it: An email message with html formatting!

HTML formatted email

If you're not into .Net, but really like TSQL then check out the next solution: Execute SQL Task with sp_send_dbmail

Case
I want to start a SSIS 2012 package from a .Net application, but I want to use project variables and project connection managers. How do you do that?

How to start that Master.dtsx package with .Net

Solution
I while ago I posted an example for the 'old fashioned' way of executing packages with .Net. This example is for the new project deployment packages in the SSISDB.

1) Create project
Start Visual Studio 2010 and create a new C# Windows Form Application project.

Select .NET Framework 4 or do that in step 2

2) Change framework version
Changed the framework version to 4 in the newley created project. Right click the project and select properties. A new window will open and there you can change the Target framework to 4. Confirm the question about changing the framework version

Changing framework version

3) Adding reference to SSIS
We need to reference 4 assemblies, but they are not in the SQL Server folder. They are only available in the GAC. The path varies a little per computer. Here are mine:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

Adding four references

4) The form
For this example I added a button and a ListBox to show the log from SSIS.

Example form

5) The code
This is the code for the start button, but don't forget the usings on top.

//C# code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;

namespace CallSSIS2012Package
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void StartPackageButton_Click(object sender, EventArgs e)
        {
            // Connection to the database server where the packages are located
            SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;");

            // SSIS server object with connection
            IntegrationServices ssisServer = new IntegrationServices(ssisConnection);

            // The reference to the package which you want to execute
            PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages["master.dtsx"];

            // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
            Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();
            executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

            // Get the identifier of the execution to get the log
            long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

            // Loop through the log and add the messages to the listbox
            foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
            {
                SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message);
            }
        }
    }
}

or VB.Net

'VB.Net code
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.IntegrationServices
Imports System.Collections.ObjectModel

Public Class Form1

    Private Sub StartPackageButton_Click(sender As System.Object, e As System.EventArgs) Handles StartPackageButton.Click
        ' Connection to the database server where the packages are located
        Dim ssisConnection As New SqlConnection("Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;")

        ' SSIS server object with connection
        Dim ssisServer As New IntegrationServices(ssisConnection)

        ' The reference to the package which you want to execute
        Dim ssisPackage As PackageInfo = ssisServer.Catalogs("SSISDB").Folders("MasterChild").Projects("MasterChildPackages").Packages("master.dtsx")

        ' Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
        Dim executionParameters As New Collection(Of PackageInfo.ExecutionValueParameterSet)
        Dim executionParameter As New PackageInfo.ExecutionValueParameterSet
        executionParameter.ObjectType = 50
        executionParameter.ParameterName = "SYNCHRONIZED"
        executionParameter.ParameterValue = 1
        executionParameters.Add(executionParameter)

        ' Get the identifier of the execution to get the log
        Dim executionIdentifier As Long = ssisPackage.Execute(False, Nothing, executionParameters)

        ' Loop through the log and add the messages to the listbox
        For Each message As OperationMessage In ssisServer.Catalogs("SSISDB").Executions(executionIdentifier).Messages
            SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message)
        Next
    End Sub
End Class

6) The result
Now run the applcation and see the result.

If you want to add parameters then take a look at my SSIS WIKI example.

* update: 30 second timeout workaround *

Case
I have a column with multiple values and I want to split them into multiple records.

Solution
You could solve it with a fancy TSQL query. I saw split functions with common table expressions, but a relatively easy script could to the trick in SSIS as well. (don't hesitate to post your query/solution in the comments)

1) Source
Add your source to the Data Flow Task

2) Script Component - input columns
This solution uses an asynchronous Script Component, so add a Script Component (type transformation) to your Data Flow Task. Edit it, go to the Input Columns pane and select all the columns you need downstream the data flow as readonly. In this case we need the columns Teacher and Students.

Input columns (readonly)

3) Script Component - output port
Go to the Inputs and Outputs pane and click on Output 0. Set the SynchronousInputID property to none to make this Script Component asynchronous.

asynchronous

4) Script Component - output columns
Add output columns for each input column that you need downstream the data flow. In this case we need Teacher (same datatype and size) and a new column named Student which will contain one value from the input column Students (same datatype, but size could probably be smaller).

Output columns

5) The script
Copy the Inputs0 _ProcessInputRow method to your script and remove all the other methods (PreExecute, PostExecute and CreateNewOutputRows) because we don't need them.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Method that will execute for each row passing
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // First we are converting the comma seperated list into a string array.
        // You can change the comma if you are using an other seperator like | or ;
        string[] Students = Row.Students.ToString().Split(new char[] { ',' }, StringSplitOptions.None);

        // Counter var used the loop through the string array
        int i = 0;

        // Looping through string array with student names
        while (i < Students.Length)
        {
            // Start a new row in the output
            Output0Buffer.AddRow();

            // Pass through all columns that you need downstream the data flow
            Output0Buffer.Teacher = Row.Teacher;

            // This is the splitted column. Take the [n] element from the array
            // and put it in the new column.
            Output0Buffer.Student = Students[i];

            // Increase counter to go the next value
            i++;
        }
    }
}

or VB.Net

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    ' Method that will execute for each row passing
    Public Overrides Sub Input0_ProcessInputRow(Row As Input0Buffer)
        ' First we are converting the comma seperated list into a string array.
        ' You can change the comma if you are using an other seperator like | or ;
        Dim Students As String() = Row.Students.ToString().Split(New Char() {","c}, StringSplitOptions.None)

        ' Counter var used the loop through the string array
        Dim i As Integer = 0

        ' Looping through string array with student names
        While i < Students.Length
            ' Start a new row in the output
            Output0Buffer.AddRow()

            ' Pass through all columns that you need downstream the data flow
            Output0Buffer.Teacher = Row.Teacher

            ' This is the splitted column. Take the [n] element from the array
            ' and put it in the new column.
            Output0Buffer.Student = Students(i)

            ' Increase counter to go the next value
            i += 1
        End While
    End Sub
End Class

6) The result
For testing purposes I added a derived column and a couple of data viewer

The result

Note: if you want to do this backwards, see this post.


Case
 I have a source where one column has a divider that splits the sales values for each month. How do I split that value over multiple columns?

Mountainbike;black;10,4,7,3,11,5,8,6,10,4,12,12 Mountainbike;blue;12,2,9,1,13,7,6,4,8,12,3,4

The last column contains the sales per month and is divided by a comma instead of a semicolon.

Solution
There are various options to split that value.
A) Substring/Findstring
B) Script Component
C) Token

Solution A: Substring/Findstring
You can use an expression in the Derived Column. The first and last deviate from the rest:
SUBSTRING(Sales,1,FINDSTRING(Sales,",",1) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",1) + 1,FINDSTRING(Sales,",",2) - FINDSTRING(Sales,",",1) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",2) + 1,FINDSTRING(Sales,",",3) - FINDSTRING(Sales,",",2) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",3) + 1,FINDSTRING(Sales,",",4) - FINDSTRING(Sales,",",3) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",4) + 1,FINDSTRING(Sales,",",5) - FINDSTRING(Sales,",",4) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",5) + 1,FINDSTRING(Sales,",",6) - FINDSTRING(Sales,",",5) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",6) + 1,FINDSTRING(Sales,",",7) - FINDSTRING(Sales,",",6) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",7) + 1,FINDSTRING(Sales,",",8) - FINDSTRING(Sales,",",7) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",8) + 1,FINDSTRING(Sales,",",9) - FINDSTRING(Sales,",",8) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",9) + 1,FINDSTRING(Sales,",",10) - FINDSTRING(Sales,",",9) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",10) + 1,FINDSTRING(Sales,",",11) - FINDSTRING(Sales,",",10) - 1)
SUBSTRING(Sales,FINDSTRING(Sales,",",11) + 1,LEN(Sales) - FINDSTRING(Sales,",",11))

Substring/Findstring solution

Solution B: Script Component
Add a Script Component (type transformation) and select the Sales column as ReadOnly input column in the Input Columns pane. Then go to the Inputs and Outputs pane and create a column foreach month. After that choose your language and hit the Edit Script button and copy the following method.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Split input column on comma fill output columns
        // Added the Convert.ToInt32(XXXX) to convert it to int
        string[] sales = Row.Sales.ToString().Split(new char[]{','}, StringSplitOptions.None);
        Row.Jan = Convert.ToInt32(sales[0]);
        Row.Feb = Convert.ToInt32(sales[1]);
        Row.Mar = Convert.ToInt32(sales[2]);
        Row.Apr = Convert.ToInt32(sales[3]);
        Row.May = Convert.ToInt32(sales[4]);
        Row.Jun = Convert.ToInt32(sales[5]);
        Row.Jul = Convert.ToInt32(sales[6]);
        Row.Aug = Convert.ToInt32(sales[7]);
        Row.Sep = Convert.ToInt32(sales[8]);
        Row.Oct = Convert.ToInt32(sales[9]);
        Row.Nov = Convert.ToInt32(sales[10]);
        Row.Dec = Convert.ToInt32(sales[11]);
    }
}

or VB.Net

'VB.Net code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()g _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Split input column on comma fill output columns
        ' Added the Convert.ToInt32(XXXX) to convert it to int
        Dim sales As String() = Row.Sales.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Row.Jan = Convert.ToInt32(sales(0))
        Row.Feb = Convert.ToInt32(sales(1))
        Row.Mar = Convert.ToInt32(sales(2))
        Row.Apr = Convert.ToInt32(sales(3))
        Row.May = Convert.ToInt32(sales(4))
        Row.Jun = Convert.ToInt32(sales(5))
        Row.Jul = Convert.ToInt32(sales(6))
        Row.Aug = Convert.ToInt32(sales(7))
        Row.Sep = Convert.ToInt32(sales(8))
        Row.Oct = Convert.ToInt32(sales(9))
        Row.Nov = Convert.ToInt32(sales(10))
        Row.Dec = Convert.ToInt32(sales(11))
    End Sub
End Class

This is more readable than the Substring/Findstring expressions!

Script Component solution

Solution C: Token
SSIS 2012 has a new expression called Token which makes life a lot easier than the Substring/Findstring solution:
TOKEN(Sales,",",1)
TOKEN(Sales,",",2)
TOKEN(Sales,",",3)
TOKEN(Sales,",",4)
TOKEN(Sales,",",5)
TOKEN(Sales,",",6)
TOKEN(Sales,",",7)
TOKEN(Sales,",",8)
TOKEN(Sales,",",9)
TOKEN(Sales,",",10)
TOKEN(Sales,",",11)
TOKEN(Sales,",",12)


Token solution

Case
I have a list of teacher and student combinations with one combination per record and I want to created a comma delimited list of students per teacher.

Solution
If your source is a database then the easiest solution is a TSQL query like this:

--TSQL Query
WITH UniqueTeachers AS
(
 SELECT  DISTINCT Teacher
 FROM  TeacherStudentTable
)
SELECT  Teacher
,   Students = STUFF((
      SELECT  ',' + Student
      FROM  TeacherStudentTable
      WHERE  Teacher = UniqueTeachers.Teacher
      ORDER BY Student
      FOR XML PATH(''), TYPE).value('.','varchar(100)'), 1, 1, '')
FROM  UniqueTeachers
ORDER BY Teacher

The query in SSIS as source

If your source is for example a flat file or a database that doesn't support a query like this, then there are also options within SSIS. For this solution I use a asynchronous Script Component.

1) Sorted source
We need a sorted source because we are comparing records with each other. In this case make sure the source is sorted on teacher first (and optional secondly on student).

Add Sort transformation if source isn't sorted

2) Script Component
Add a Script Component (type transformation) and select the Teacher and Student columns as ReadOnly input columns.

Input columns: Teacher and Student

3) Asynchronous
We need to make the Script Component asynchronous because it throws out a different number of rows than there are incomming. Go to the Inputs and Outputs pane, click on Output 0 and change the SynchronousInputID to None.

Asynchonous

4) Output
We now need to create an output for the Script Component. Expand the Output 0 and add two columns:
Teacher (same data type and size as the input column teacher)
Students (same data type as the input column student, but larger to fit multiple student names)

Output columns

5) The Script
Copy the three variables and the two methods to your Script Component (and remove any other existing methods).

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    bool initialRow = true;     // Indicater for the first row
    string teacher = "";        // Name of the teacher to track teacherchanges between rows
    string students = "";       // The comma delimited list of students

    public override void Input0_ProcessInput(Input0Buffer Buffer)
    {
        // Loop through buffer
        while (Buffer.NextRow())
        {
            // Process an input row
            Input0_ProcessInputRow(Buffer);

            // Change the indicator after the first row has been processed
            initialRow = false;
        }
        
        // Check if this is the last row
        if (Buffer.EndOfRowset())
        {
            // Fill the columns of the existing output row with values
            // from the variable before closing this Script Component
            Output0Buffer.Teacher = teacher;
            Output0Buffer.Students = students;
        }
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (initialRow)
        {
            // This is for the first input row only
            
            // Create a new output row
            Output0Buffer.AddRow();

            // Now fill the variables with the values from the input row
            teacher = Row.Teacher;
            students = Row.Student;
        }
        else if ((!initialRow) & (teacher != Row.Teacher))
        {
            // This isn't the first row, but the teacher did change

            // Fill the columns of the existing output row with values
            // from the variable before creating a new output row
            Output0Buffer.Teacher = teacher;
            Output0Buffer.Students = students;

            // Create a new output row
            Output0Buffer.AddRow();

            // Now fill the variables with the values from the input row
            teacher = Row.Teacher;
            students = Row.Student;
        }
        else if ((!initialRow) & (teacher == Row.Teacher))
        {
            // This isn't the first row, and the teacher did not change

            // Concatenate the studentsname to the variable
            students += "," + Row.Student;
        }
    }

    // Little explanation:
    // Rows are created in memory with .AddRow()
    // and will be submitted to the output when a
    // new / subsequent row is created or when
    // the last buffer has been finished.
}

or in VB.Net

'VB.Net code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Private initialRow As Boolean = True    ' Indicater for the first row
    Private teacher As String = ""          ' Name of the teacher to track teacherchanges between rows
    Private students As String = ""         ' The comma delimited list of students

    Public Overrides Sub Input0_ProcessInput(Buffer As Input0Buffer)
        ' Loop through buffer
        While Buffer.NextRow()
            ' Process an input row
            Input0_ProcessInputRow(Buffer)

            ' Change the indicator after the first row has been processed
            initialRow = False
        End While

        ' Check if this is the last row
        If Buffer.EndOfRowset() Then
            ' Fill the columns of the existing output row with values
            ' from the variable before closing this Script Component
            Output0Buffer.Teacher = teacher
            Output0Buffer.Students = students
        End If
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(Row As Input0Buffer)
        If initialRow Then
            ' This is for the first input row only

            ' Create a new output row
            Output0Buffer.AddRow()

            ' Now fill the variables with the values from the input row
            teacher = Row.Teacher
            students = Row.Student
        ElseIf (Not initialRow) And (teacher <> Row.Teacher) Then
            ' This isn't the first row, but the teacher did change

            ' Fill the columns of the existing output row with values
            ' from the variable before creating a new output row
            Output0Buffer.Teacher = teacher
            Output0Buffer.Students = students

            ' Create a new output row
            Output0Buffer.AddRow()

            ' Now fill the variables with the values from the input row
            teacher = Row.Teacher
            students = Row.Student
        ElseIf (Not initialRow) And (teacher = Row.Teacher) Then
            ' This isn't the first row, and the teacher did not change

            ' Concatenate the studentsname to the variable
            students += "," & Convert.ToString(Row.Student)
        End If
    End Sub

    ' Little explanation:
    ' Rows are created in memory with .AddRow()
    ' and will be submitted to the output when a
    ' new / subsequent row is created or when
    ' the last buffer has been finished.
End Class

Note: You can change the delimiter in C# line 70 and VB.Net line 64.


6) Testing
For testing purposes I added a derived column and a couple of data viewer.

The result



Conclusion: both methods have the same result. For a SQL database source, the T-SQL method is probably a little faster. If you can do the sort in the source the performance differences will diminish.

In one of my next blog posts I will show you how to split a comma separated list in to records.