Sorting in SQL vs sorting in SSIS

Case
When I join two flows in SSIS, the flows need to be sorted. According to the Performance Best Practices I try to sort them in the source components because that's better for performance. Ofcource that's not always possible, for instance when your source is a flat file, and then you need the SSIS sort component.

But watch out when combining these two sort methods. Because SSIS and SQL don't sort the same way! The sort of SSIS is case sensitive and the sort in SQL Server is case insensitive. See the result when you combine these two. This will result in unwanted situations and missing joins. Is there a solution?

Click to enlarge

Solution
This has to do with the different sorting collations. SSIS uses the Windows collation (Case-Sensitive) and SQL Server uses SQL collation (Case-Insensitive, by default). You can either adjust the SQL sort to SSIS or the SSIS sort to SQL.

Adjust SQL Sort to SSIS: Case-Sensitive ORDER BY
You can solve this by changing the source query of the sorted source:

-- Notice the extra COLLATE
SELECT     myStringColumn
FROM       myTable
ORDER BY   myStringColumn
COLLATE    Latin1_General_CS_AS_WS

The suffix CS_AS_WS stands for Case-sensitive, accent-sensitive, kana-insensitive, width-sensitive. See the compleet suffix list at msdn. This new query will result in a correct join in SSIS.

Both the same sort

Adjust SSIS Sort to SQL: Case-Insensitive Sort Transformation
Important: This solution has one side affect. The merge join will also be Case Insensitive!

1) Source
In the sorted OLE DB source you already used the advanced editor to tell SSIS this source is sorted with an ORDER BY in the query, but you now also need to tell SSIS that it is sorted Case-Insensitive by setting the ComparisonFlags property to Ignore case. Note: this doesn't change the actual sorting. You are just telling SSIS how it is sorted.

Indicate that source is sorted Case-Insensitive

2) Sort
In the SORT Transformation you also need to set the Comparison Flags property to Ignore case. Note: This will change the actual sorting.


Setting the Comparison Flags property to Ignore case

3) Result
Now both flows are sorted the same and the Merge Join works as a Case-Insensitive Inner join (similar to a T-SQL INNER JOIN).

Like a T-SQL Inner Join

Get file properties with SSIS

Case
I recently did a post on how to get filedates in SSIS, but I got questions about how to get other file properties like size, owner and a check whether the file is locked by an other process.

Solution
You could use the open source File Properties Task, but you can also do it yourself with a Script Task. Let's elaborate that Script Task solution.

1) Create variables
I created a whole bunch of variables, one for each file property. Notice the various datatypes. Fill the variable FilePath with a value.

Right click in your Control Flow to activate Variable window

2) Script Task
Add a Script Task to your Control Flow. I didn't add the variables to the readonly and readwrite lists of the Script Task, but locked them in the script. See this article for the difference between those methods.

Script Task

2a) Optional
This step is optional. It depends on whether you choose script 3a or 3b. This step is required for script 3a.
Select all the new variables as ReadWriteVariables. Only the FilePath can be a ReadOnlyVariable.

ReadOnly- and ReadWriteVariables

3a) The script (if you used 2a)
Copy the following code (usings and method) to your Script Task and remove the properties you don't need.

// C# code
// Fill SSIS variables with file properties
using System;
using System.Data;
using System.IO;                        // Added to get file properties
using System.Security.Principal;        // Added to get file owner
using System.Security.AccessControl;    // Added to get file owner
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_9ef66c631df646e08e4184e34887da16.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()
        {
            // Variable for file information
            FileInfo fileInfo;

            // Fill fileInfo variable with file information
            fileInfo = new FileInfo(Dts.Variables["User::FilePath"].Value.ToString());

            // Check if file exists
            Dts.Variables["User::FileExists"].Value = fileInfo.Exists;

            // Get the rest of the file properties if the file exists
            if (fileInfo.Exists)
            {
                // Get file creation date
                Dts.Variables["User::FileCreationDate"].Value = fileInfo.CreationTime;
                
                // Get last modified date
                Dts.Variables["User::FileLastModifiedDate"].Value = fileInfo.LastWriteTime;

                // Get last accessed date
                Dts.Variables["User::FileLastAccessedDate"].Value = fileInfo.LastAccessTime;

                // Get size of the file in bytes
                Dts.Variables["User::FileSize"].Value = fileInfo.Length;

                // Get file attributes
                Dts.Variables["User::FileAttributes"].Value = fileInfo.Attributes.ToString();
                Dts.Variables["User::FileIsReadOnly"].Value = fileInfo.IsReadOnly;
                
                //////////////////////////////////////////////////////
                // Check if the file isn't locked by an other process
                try
                {
                    // Try to open the file. If it succeeds, set variable to false and close stream
                    FileStream fs = new FileStream(Dts.Variables["User::FilePath"].Value.ToString(), FileMode.Open);
                    Dts.Variables["User::FileInUse"].Value = false;
                    fs.Close();
                }
                catch (Exception ex)
                {
                    // If opening fails, it's probably locked by an other process
                    Dts.Variables["User::FileInUse"].Value = true;

                    // Log actual error to SSIS to be sure 
                    Dts.Events.FireWarning(0, "Get File Properties", ex.Message, string.Empty, 0);
                }

                //////////////////////////////////////////////////////
                // Get the Windows domain user name of the file owner
                FileSecurity fileSecurity = fileInfo.GetAccessControl();
                IdentityReference identityReference = fileSecurity.GetOwner(typeof(NTAccount));
                Dts.Variables["User::FileOwner"].Value = identityReference.Value;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

or the VB.Net code

' VB.net Code
' Fill SSIS variables with file properties 
Imports System
Imports System.Data
Imports System.IO                        ' Added to get file properties
Imports System.Security.Principal        ' Added to get file owner
Imports System.Security.AccessControl    ' Added to get file owner 
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

 Public Sub Main()
        ' Variable for file information 
        Dim fileInfo As FileInfo

        ' Fill fileInfo variable with file information 
        fileInfo = New FileInfo(Dts.Variables("User::FilePath").Value.ToString())

        ' Check if file exists 
        Dts.Variables("User::FileExists").Value = fileInfo.Exists

        ' Get the rest of the file properties if the file exists 
        If (fileInfo.Exists) Then
            ' Get file creation date 
            Dts.Variables("User::FileCreationDate").Value = fileInfo.CreationTime
            ' Get last modified date 
            Dts.Variables("User::FileLastModifiedDate").Value = fileInfo.LastWriteTime
            ' Get last accessed date 
            Dts.Variables("User::FileLastAccessedDate").Value = fileInfo.LastAccessTime
            ' Get size of the file in bytes 
            Dts.Variables("User::FileSize").Value = fileInfo.Length
            ' Get file attributes 
            Dts.Variables("User::FileAttributes").Value = fileInfo.Attributes.ToString()
            Dts.Variables("User::FileIsReadOnly").Value = fileInfo.IsReadOnly
            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Check if the file isn't locked by an other process 
            Try
                ' Try to open the file. If it succeeds, set variable to false and close stream 
                Dim fs As FileStream = New FileStream(Dts.Variables("User::FilePath").Value.ToString(), FileMode.Open)
                Dts.Variables("User::FileInUse").Value = False
                fs.Close()
            Catch ex As Exception
                ' If opening fails, it's probably locked by an other process 
                Dts.Variables("User::FileInUse").Value = True

                ' Log actual error to SSIS to be sure 
                Dts.Events.FireWarning(0, "Get File Properties", ex.Message, String.Empty, 0)
            End Try

            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Get the Windows domain user name of the file owner 
            Dim fileSecurity As FileSecurity = fileInfo.GetAccessControl()
            Dim identityReference As IdentityReference = fileSecurity.GetOwner(GetType(NTAccount))
            Dts.Variables("User::FileOwner").Value = identityReference.Value
        End If

        Dts.TaskResult = ScriptResults.Success
 End Sub

End Class

3b) The script (without 2a)
Copy the following code (usings and method) to your Script Task and remove the properties you don't need.

// C# code
// Fill SSIS variables with file properties
using System;
using System.Data;
using System.IO;                        // Added to get file properties
using System.Security.Principal;        // Added to get file owner
using System.Security.AccessControl;    // Added to get file owner
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_cb8dd466d98149fcb2e3852ead6b6a09.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()
        {
            // Lock SSIS variables
            Dts.VariableDispenser.LockForRead("User::FilePath");

            Dts.VariableDispenser.LockForWrite("User::FileAttributes");
            Dts.VariableDispenser.LockForWrite("User::FileCreationDate");
            Dts.VariableDispenser.LockForWrite("User::FileExists");
            Dts.VariableDispenser.LockForWrite("User::FileInUse");
            Dts.VariableDispenser.LockForWrite("User::FileIsReadOnly");
            Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate");
            Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate");
            Dts.VariableDispenser.LockForWrite("User::FileOwner");
            Dts.VariableDispenser.LockForWrite("User::FileSize");

            // Create a variables 'container' to store variables
            Variables vars = null;

            // Add variables from the VariableDispenser to the variables 'container'
            Dts.VariableDispenser.GetVariables(ref vars);

            // Variable for file information
            FileInfo fileInfo;

            // Fill fileInfo variable with file information
            fileInfo = new FileInfo(vars["User::FilePath"].Value.ToString());

            // Check if file exists
            vars["User::FileExists"].Value = fileInfo.Exists;

            // Get the rest of the file properties if the file exists
            if (fileInfo.Exists)
            {
                // Get file creation date
                vars["User::FileCreationDate"].Value = fileInfo.CreationTime;
                
                // Get last modified date
                vars["User::FileLastModifiedDate"].Value = fileInfo.LastWriteTime;

                // Get last accessed date
                vars["User::FileLastAccessedDate"].Value = fileInfo.LastAccessTime;

                // Get size of the file in bytes
                vars["User::FileSize"].Value = fileInfo.Length;

                // Get file attributes
                vars["User::FileAttributes"].Value = fileInfo.Attributes.ToString();
                vars["User::FileIsReadOnly"].Value = fileInfo.IsReadOnly;
                
                //////////////////////////////////////////////////////
                // Check if the file isn't locked by an other process
                try
                {
                    // Try to open the file. If it succeeds, set variable to false and close stream
                    FileStream fs = new FileStream(vars["User::FilePath"].Value.ToString(), FileMode.Open);
                    vars["User::FileInUse"].Value = false;
                    fs.Close();
                }
                catch (Exception ex)
                {
                    // If opening fails, it's probably locked by an other process
                    vars["User::FileInUse"].Value = true;

                    // Log actual error to SSIS to be sure 
                    Dts.Events.FireWarning(0, "Get File Properties", ex.Message, string.Empty, 0);
                }

                //////////////////////////////////////////////////////
                // Get the Windows domain user name of the file owner
                FileSecurity fileSecurity = fileInfo.GetAccessControl();
                IdentityReference identityReference = fileSecurity.GetOwner(typeof(NTAccount));
                vars["User::FileOwner"].Value = identityReference.Value;
            }

            // Release the locks
            vars.Unlock();
            
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

or the VB.Net code

' VB.net Code
' Fill SSIS variables with file properties 
Imports System
Imports System.Data
Imports System.IO                        ' Added to get file properties
Imports System.Security.Principal        ' Added to get file owner
Imports System.Security.AccessControl    ' Added to get file owner 
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

    Public Sub Main()
        ' Lock SSIS variables 
        Dts.VariableDispenser.LockForRead("User::FilePath")

        Dts.VariableDispenser.LockForWrite("User::FileAttributes")
        Dts.VariableDispenser.LockForWrite("User::FileCreationDate")
        Dts.VariableDispenser.LockForWrite("User::FileExists")
        Dts.VariableDispenser.LockForWrite("User::FileInUse")
        Dts.VariableDispenser.LockForWrite("User::FileIsReadOnly")
        Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate")
        Dts.VariableDispenser.LockForWrite("User::FileOwner")
        Dts.VariableDispenser.LockForWrite("User::FileSize")

        ' Create a variables 'container' to store variables 
        Dim vars As Variables = Nothing

        ' Add variables from the VariableDispenser to the variables 'container' 
        Dts.VariableDispenser.GetVariables(vars)

        ' Variable for file information 
        Dim fileInfo As FileInfo

        ' Fill fileInfo variable with file information 
        fileInfo = New FileInfo(vars("User::FilePath").Value.ToString())

        ' Check if file exists 
        vars("User::FileExists").Value = fileInfo.Exists

        ' Get the rest of the file properties if the file exists 
        If (fileInfo.Exists) Then
            ' Get file creation date 
            vars("User::FileCreationDate").Value = fileInfo.CreationTime
            ' Get last modified date 
            vars("User::FileLastModifiedDate").Value = fileInfo.LastWriteTime
            ' Get last accessed date 
            vars("User::FileLastAccessedDate").Value = fileInfo.LastAccessTime
            ' Get size of the file in bytes 
            vars("User::FileSize").Value = fileInfo.Length
            ' Get file attributes 
            vars("User::FileAttributes").Value = fileInfo.Attributes.ToString()
            vars("User::FileIsReadOnly").Value = fileInfo.IsReadOnly
            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Check if the file isn't locked by an other process 
            Try
                ' Try to open the file. If it succeeds, set variable to false and close stream 
                Dim fs As FileStream = New FileStream(vars("User::FilePath").Value.ToString(), FileMode.Open)
                vars("User::FileInUse").Value = False
                fs.Close()
            Catch ex As Exception
                ' If opening fails, it's probably locked by an other process 
                vars("User::FileInUse").Value = True

                ' Log actual error to SSIS to be sure 
                Dts.Events.FireWarning(0, "Get File Properties", ex.Message, String.Empty, 0)
            End Try

            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Get the Windows domain user name of the file owner 
            Dim fileSecurity As FileSecurity = fileInfo.GetAccessControl()
            Dim identityReference As IdentityReference = fileSecurity.GetOwner(GetType(NTAccount))
            vars("User::FileOwner").Value = identityReference.Value
        End If

        ' Release the locks 
        vars.Unlock()

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

4) The result

For testing purposes I added an other Script Task with a messagebox that reads all the variables.

The result

5) Usage
You can check for example whether a file locked before you start the Data Flow. You do this with an expression on the Precedence Constraint.


Expression

Note: This script task example can be refined with error handling and logging.

Cleaning with Regular Expressions in SSIS

Case
I have a column with html tags in the value. How can I remove those tags?

Solution
You could try the make an expression in a derived column, but it would probably become an unreadable expression. There are a couple of alternatives like the Konesans RegexClean Transformation. Links to more third party and open source components are availble in a previous article. And if your source is a database then you could use a user defined function, but  in this case we will be using a .Net Regular Expression within a Script Component to remove the html tags.

File example:

"Name";"Message" "Joost";"Hallo, <strong>this</strong> is bold." "William";"This is my homepage: <a href='http://microsoft-ssis.blogspot.com/'>http://microsoft-ssis.blogspot.com/</a>."

1) Source
Add a Flat File Source Component for the textfile above.

Flat File Source

2) Script Component
Add a Script Component type transformation below the Flat File Source and give it a suitable name.

Script Component - transformation

3) Input Column
Edit the Script Component and select the message column as ReadWrite on the Input Columns tab.

Message as ReadWrite input column

4) The Script
SSIS creates some standard methods, but we only use Input0_ProcessInputRow. You can remove the rest. We are also adding a new method that removes the html tags. It uses a .Net Regex method. You can search the web for your own regex pattern and change it in the extra method.

// C# code
using System;
using System.Data;
using System.Text.RegularExpressions;    // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Method that will be executed for each row.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Replace the value of the message column
        Row.Message = RemoveHtml(Row.Message);
    }

    // A string method that removes html tags
    // with a regex pattern
    public String RemoveHtml(String message)
    {
        // The pattern for a html tag
        String htmlTagPattern = "<(.|\n)+?>";
        // Create a regex object with the pattern 
        Regex objRegExp = new Regex(htmlTagPattern);
        // Replace html tag by an empty string
        message = objRegExp.Replace(message, String.Empty);
        // Return the message without html tags
        return message;
    }
}

or VB.Net

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

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

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Replace the value of the message column
        Row.Message = RemoveHtml(Row.Message)
    End Sub

    ' A string method that removes html tags
    ' with a regex pattern
    Public Function RemoveHtml(message As [String]) As [String]
        ' The pattern for a html tag
        Dim htmlTagPattern As [String] = "<(.|" & vbLf & ")+?>"
        ' Create a regex object with the pattern 
        Dim objRegExp As New Regex(htmlTagPattern)
        ' Replace html tag by an empty string
        message = objRegExp.Replace(message, [String].Empty)
        ' Return the message without html tags
        Return message
    End Function
End Class

Note: this script uses a very simple regular expression. Search the web for other/better regular expressions.

5) The Result
For testing purposes I added a Derived Columns and two data viewes on the Data Flow Paths.

SSIS 2008 Package example

Regular Expressions in SSIS

Case
I want to use a Regular Expression to filter incorrect emailaddresses in SSIS.

Solution
There are a couple of options:

• Microsoft built a SSIS Sample Component for regex, but it's only available for 2005.
• Konesans built a RegexClean Transformation and a Regular Expression Transformation.
• There is also an open source project named RegExtractor SSIS Component.
• And you can even do it with Regular Expressions in T-SQL.
• But you can also do it your self with a Script Component Transformation.

Let's elaborate the last option and use this file as a source:

"Name";"emailaddress" "Joost";"joost@blogger.com" "John";"john@.nl" "Max";"max@gmail.com" "William";"william@hotmail,com" "Bill";"b.gates@microsoft.com"

1) Add source
Add a Flat File Source Component in your dataflow for the file example above.

Flat File mailinglist

2) Add Script Component

Add a Script Component, type transformation, after your Flat File Source Component.

Script Component (transformation)

3) Input Columns

 Edit the Script Component and select the email address column as an input column (readonly).

Input Columns tab

4) Outputs

Change the name of the default output port to CorrectEmail and set the ExclusionGroup to 1. Add an extra output port for the IncorrectEmail and set the ExclusionGroup to 1. Also select the input port as the SynchronousInputID. Now you have two output ports. One for the correct and one for the incorrect email address.

Inputs and Outputs tab

4) The Script

Hit the Edit Script button to open the VSTA editor. SSIS creates some standard methods, but we only use Input0_ProcessInputRow. You can remove the rest of the methods. We are also adding a new method that validates the email address. It uses a .Net Regex method. You can search the web for your own regex pattern and change it in the extra method.

// C# code
using System;
using System.Data;
using System.Text.RegularExpressions;   // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    // Method that will be executed for each row.
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Validate the email address
        if (IsCorrectEmail(Row.Email))
        {
            // Redirect the correct email address
            Row.DirectRowToCorrectEmail();
        }
        else
        {
            // Redirect the incorrect email address
            Row.DirectRowToInCorrectEmail();
        }
    }
    
    // A boolean method that validates an email address
    // with a regex pattern.
    public bool IsCorrectEmail(String emailAddress)
    {
        // The pattern for email
        string emailAddressPattern = @"^(([^<>()[\]\\.,;:\s@\""]+"
                        + @"(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@"
                        + @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
                        + @"\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+"
                        + @"[a-zA-Z]{2,}))$";
        // Create a regex object with the pattern
        Regex emailAddressRegex = new Regex(emailAddressPattern);
        // Check if it is match and return that value (boolean)
        return emailAddressRegex.IsMatch(emailAddress);
    }
}

' VB.Net code (SSIS 2008)
Imports System
Imports System.Data
Imports System.Math
Imports System.Text.RegularExpressions    ' Added
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 _
 _
Public Class ScriptMain
    Inherits UserComponent

    ' Method that will be executed for each row.
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Validate the email address
        If (IsCorrectEmail(Row.Email)) Then
            ' Redirect the correct email address
            Row.DirectRowToCorrectEmail()
        Else
            ' Redirect the incorrect email address
            Row.DirectRowToIncorrectEmail()
        End If
    End Sub

    ' A boolean method that validates an email address
    ' with a regex pattern.
    Public Function IsCorrectEmail(ByVal emailAddress As String) As Boolean
        ' The pattern for email
        Dim emailAddressPattern As String = "^(([^<>()[\]\\.,;:\s@\""]+" + _
        "(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@" + _
        "((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}" + _
        "\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+" + _
        "[a-zA-Z]{2,}))$"

        ' Create a regex object with the pattern
        Dim emailAddressRegex As Regex = New Regex(emailAddressPattern)

        ' Check if it is match and return that value (boolean)
        Return emailAddressRegex.IsMatch(emailAddress)
    End Function

End Class

5) The result

For testing purposes I added two Derived Columns with data viewes on the Data Flow Paths.

The result

Jamie Thomson, alias SSIS Junkie, has an other example in VB.net available. And you can also use Regular Expression to clean column values. More about that is described in this article.

RowCount for Execute SQL Task

Case
How do you get a rowcount when you execute an Insert, Update or Delete query with an Execute SQL Task? I want to log the number of effected rows just like in a Data Flow.

Solution
The Transact-SQL function @@ROWCOUNT can help you here. It returns the number of rows affected by the last statement.

1) Variable
Create an integer variable named 'NumberOfRecords' to store the number of affected rows in.

Right click to show variables

2) Execute SQL Task
Put an Execute SQL Task on your Control Flow. We are going to update some records.

Give it a suitable name.

3) Edit Execute SQL Statement
On the general tab, change the resultset to Single Row and select the right connection (this function only works for SQL Server).

Resultset: Single Row

4) SQLStatement
Enter your query, but add the following text at the bottum of your query: SELECT @@ROWCOUNT as NumberOfRecords; This query will return the number of affected rows in the column NumberOfRecords.

See the @@ROWCOUNT function

5) Result Set
Go to the Result Set tab and change the Result Name to NumberOfRecords. This is the name of the column. Select the variable of step 1 to store the value in.

Result Set

6) The Result
To show you the value of the variable with the number of affected records, I added a Script Task with a simple messagebox. You can add your own logging. For example a Script Task that fires an event or an Execute SQL Task that inserts some logging record.

The Result