Redirect duplicate rows

Case
I have duplicate records in my source which I want to redirect to a separate destination in my data flow task. Most solutions like the Aggregate Transformation or the Sort Transformation with "Remove rows with duplicate sort values"  selected, will remove the records instead of redirecting them.

Example: If there are three identical records (two columns), then two of them should be redirected as duplicate so that I can capture them in an error destination.

Solution
This solution uses a TSQL order by or a SSIS sort in combination with a Script Component to detect and redirect all duplicate records.

If your source is a SQL Server table you could probably come up with a fancy TSQL deduplication script to do the same in less processing time, but this solution is easy and also works for other kind of sources. Even if for example you only want to compare two out of five columns.

1) Source and Sort
Add your source to the Data Flow Task. You need to sort on the columns you want to use for deduplication. If your source is a database table, then you should add a GROUP BY clause to the query else you should add a SORT Transformation right after your source.

Add a sorted source

2) Add Script Component
Add a Script Component type Transformation after your sorted source and give it a suitable name.

Add Script Component

3) Edit Script Component: Input Columns
Edit the Script Component and go to the Input Columns tab and select all the columns you want to use for deduplication as ReadOnly (the same fields as you used for sorting).

Select columns.Type doesn't matter.

4) Edit Script Components: Inputs and Outputs, Edit Output
Go to the Inputs and Outputs tab and change the name of the Output port from "Output 0" to "Unique". Also change the ExclusionGroup to 1.

Rename default output port

5) Edit Script Components: Inputs and Outputs, New output
Add a new Output and give it the name Duplicate and change the ExlusionGroup to 1. To connect this new output port to the input port, change the SynchronousInputID property and select the input port.

New Output port

6) The Script
Edit the Script and copy the following code. This script uses reflection to get all selected columns so that you don't have to change the script if you change the input columns. But read the coding comments.

// C# code
// This script automaticly compares the selected columns, but there is one 'bug':
// You have to edit and close this script again if you change input columns.
using System;
using System.Data;
using System.Reflection;                                // Added
using System.Text;                                      // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Create a variable to store the concatenated values for the previous row
    string previousRow = "";

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create a variable to store the concatenated values of current row
        StringBuilder currentRow = new StringBuilder();

        foreach (PropertyInfo p in Row.GetType().GetProperties())
        {
            // We can't use the _IsNull columns, so ignore them. Also ignore the new output column
            if ((p.Name.ToLower().EndsWith("_IsNull") == false) && (p.Name.Equals("Duplicate") == false))
            {
                try
                {
                    // Concatenate value as string to variable
                    currentRow.Append(p.GetValue(Row, null).ToString() + "|");
                }
                catch (ArgumentException)
                {
                    // If the value is NULL (empty) then you can't get the value of it
                    currentRow.Append("NULL|");
                }
                catch (Exception ex)
                {
                    // Raise error because something unexpected went wrong
                    bool pbCancel = false;
                    this.ComponentMetaData.FireError(0, "MarkDuplicates", p.Name + ": " + ex.Message, string.Empty, 0, out pbCancel);
                }
            }
        }

        // Check if the current row and previous row are the same
        if (currentRow.ToString().Equals(previousRow))
        {
            // Redirect to duplicate output
            Row.DirectRowToDuplicate();
        }
        else
        {
            // Redirect to unique output
            Row.DirectRowToUnique();
        }

        // Fill previous row with current value for next check
        previousRow = currentRow.ToString();
    }
}

or VB.Net

' VB.Net code
' This script automaticly compares the selected columns, but there is one 'bug':
' You have to edit and close this script again if you change input columns.

Imports System
Imports System.Data
Imports System.Math
Imports System.Reflection                           ' Added
Imports System.Text                                 ' 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

    ' Create a variable to store the concatenated values for the previous row
    Dim previousRow As String = ""

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Create a variable to store the concatenated values of current row
        Dim currentRow As StringBuilder = New StringBuilder()

        Dim p As PropertyInfo
        For Each p In Row.GetType().GetProperties()
            ' We can't use the _IsNull columns, so ignore them. Also ignore the new output column
            If ((p.Name.ToLower().EndsWith("_IsNull") = False) And (p.Name.Equals("Duplicate") = False)) Then
                Try
                    ' Concatenate value as string to variable
                    currentRow.Append(p.GetValue(Row, Nothing).ToString() + "|")
                Catch ex As ArgumentException
                    ' If the value is NULL (empty) then you can't get the value of it
                    currentRow.Append("NULL|")
                Catch ex As Exception
                    ' Raise error because something unexpected went wrong
                    Dim pbCancel As Boolean
                    Me.ComponentMetaData.FireError(0, "MarkDuplicates", p.Name + ": " + ex.Message, String.Empty, 0, pbCancel)
                End Try
            End If
        Next

        ' Check if the current row and previous row are the same
        If (currentRow.ToString().Equals(previousRow)) Then
            ' Redirect to duplicate output
            Row.DirectRowToDuplicate()
        Else
            ' Redirect to unique output
            Row.DirectRowToUnique()
        End If

        ' Fill previous row with current value for next check
        previousRow = currentRow.ToString()
    End Sub
End Class

6) Destinations
Add two destinations and connect the Data Flow Paths to the destinations. When connecting you will have to select the output port.

Choose right output

7) Testing
I added some dataviewers for testing purposes and selected the first three columns for deduplication.

The Result

All roads lead to Rome, so let me know what your solution is!
Update: here is an other cool solution that doesn't need sorting.

Flexible XML Destination

Case
A client wants to export data to XML files, but SSIS has no XML destination. A script component could help him but he doesn't want to create a new script for each file/source just because the file/source lay-out is different.

Starting point

Col1	Col2	Col3
test	Test	TEST
bla	Bla	BLA
xxx	Xxx	XXX

Desired situation
<?xml version="1.0" ?>
<ROOT>
 <ROW>
  <col1>test</col1>
  <col2>Test</col2>
  <col3>TEST</col3>
 </ROW>
 <ROW>
  <col1>bla</col1>
  <col2>Bla</col2>
  <col3>BLA</col3>
 </ROW>
 <ROW>
  <col1>xxx</col1>
  <col2>Xxx</col2>
  <col3>XXX</col3>
 </ROW>
</ROOT>

Solution
There is a request for an XML destination on the Microsoft site which you can support. There is an open source component: XML Destination and there are some third party components, but you can do it yourself with a Script component.
Let's create a flexible XML destination that is the same for each file/source. This could be resolved by reflection (again).

1) Source
Create a random source (flatfile / OLE DB).

2) Script component
Drag a script component to your data flow and select Destination. Connect it to the Source component.

Script component destination

3) Input columns
Edit the Script components and select all input columns (or just the ones you need)

Select all columns as input

4) Destination
Create a new connection named xmldocument in the Connection Managers tab. The file type should be FILE and create a new file.

FILE

Create file

5) The Script
Now the script (C# and VB.net for this example). SSIS creates 3 methods which we will adjust and we need a fourth method to remove forbidden xml characters such as < and >.

// C# Code
// Flexible script that creates a XML document
// using the SSIS columns as nodes.
using System;
using System.Data;
using System.Reflection; // Added
using System.Xml;        // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Variable that contains the XML document
    XmlTextWriter textWriter;

    // Start of XML document
    public override void PreExecute()
    {
        base.PreExecute();

        // Create a new XML document and use the filepath in the connection as XML-file
        textWriter = new XmlTextWriter(this.Connections.xmldocument.ConnectionString.ToString(), null);

        // Start writing the XML document: 
        textWriter.WriteStartDocument();

        // Create root element 
        textWriter.WriteStartElement("ROOT");
    }

    // Close of XML document
    public override void PostExecute()
    {
        base.PostExecute();

        // Close root element: 
        textWriter.WriteEndElement();

        // Stop writing the XML document
        textWriter.WriteEndDocument();

        // Close document
        textWriter.Close();
    }

    // Method that will be started for each record in you dataflow  
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Row type to get the value of a column
        Type rowType = Row.GetType();
        String columnValue = "";

        // Create row element: 
        textWriter.WriteStartElement("ROW");

        // Loop through all columns and create a column element: valuevalue
        foreach (IDTSInputColumn100 column in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
        {
            // Use the SSIS column name as element name: 
            textWriter.WriteStartElement(column.Name);
            
            // Get column value, will fail if null
            try
            {
                columnValue = rowType.GetProperty(column.Name).GetValue(Row, null).ToString();
            }
            catch
            {
                // Default value for null values: "null", "" or null
                columnValue = "null";
            }
            finally
            {
                textWriter.WriteString(removeForbiddenXmlChars(columnValue));
               }
            // Close column element: 
            textWriter.WriteEndElement();
        }
        // Close row element: 
        textWriter.WriteEndElement();


        // Output the number of processed rows. 103 = RowsWritten
        this.ComponentMetaData.IncrementPipelinePerfCounter(103, 1); 
    }

    // Remove forbidden chars that could damage your XML document
    private string removeForbiddenXmlChars(string columnValue)
    {
        return columnValue.Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;");
    }
}

And the VB.net code

' VB.Net code
' Flexible script that creates a XML document 
' using the SSIS columns as nodes. 
Imports System
Imports System.Data
Imports System.Reflection       ' Added
Imports System.Xml              ' Added
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

    Dim textWriter As XmlTextWriter

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        ' Create a new XML document and use the filepath in the connection as XML-file 
        textWriter = New XmlTextWriter(Me.Connections.xmldocument.ConnectionString.ToString(), System.Text.Encoding.Default)

        'Start writing the XML document:
        textWriter.WriteStartDocument()

        'Create root element 
        textWriter.WriteStartElement("ROOT")
    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()

        'Close root element: 
        textWriter.WriteEndElement()

        'Stop writing the XML document
        textWriter.WriteEndDocument()

        'Close document
        textWriter.Close()
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Row type to get the value of a column 
        Dim rowType As Type = Row.GetType()
        Dim columnValue As String = ""

        ' Create row element:  
        textWriter.WriteStartElement("ROW")

        ' Loop through all columns and create a column element: valuevalue 
        For Each column As IDTSInputColumn100 In Me.ComponentMetaData.InputCollection(0).InputColumnCollection


            ' Use the SSIS column name as element name:  
            textWriter.WriteStartElement(column.Name)

            ' Get column value, will fail if null 
            Try
                columnValue = rowType.GetProperty(column.Name).GetValue(Row, Nothing).ToString()
            Catch
                ' Default value for null values: "null", "" or null 
                columnValue = "null"
            Finally
                textWriter.WriteString(removeForbiddenXmlChars(columnValue))
            End Try

            ' Close column element:  
            textWriter.WriteEndElement()
        Next
        ' Close row element:  
        textWriter.WriteEndElement()
        ' Output the number of processed rows. 103 = RowsWritten 
        Me.ComponentMetaData.IncrementPipelinePerfCounter(103, 1)

    End Sub


    'Remove forbidden chars that could damage your XML document
    Private Function removeForbiddenXmlChars(ByVal columnValue As String) As String
        removeForbiddenXmlChars = columnValue.Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;")
    End Function

End Class

Note: there are probably some more forbidden XML chars like ' (&apos;) and " (&quot;) that you need to replace.


UPDATE:
I have a newer version which doesn't use the ugly try catch for null values, but you have to change the .Net Framework version to 3.5.

// C# Code
// Flexible script that creates a XML document 
// using the SSIS columns as nodes. 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;    // Added 
using System.Xml;           // Added 
using System.Linq;          // Added

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Variable that contains the XML document 
    XmlTextWriter textWriter;
    
    // Start of XML document 
    public override void PreExecute()
    {
        base.PreExecute();
        // Create a new XML document and use the filepath in the connection as XML-file 
        textWriter = new XmlTextWriter(this.Connections.xmldocument.ConnectionString.ToString(), null);
        // Start writing the XML document: 
        textWriter.WriteStartDocument();
        // Create root element  
        textWriter.WriteStartElement("ROOT");
    }

    // Close of XML document 
    public override void PostExecute()
    {
        base.PostExecute();
        // Close root element:  
        textWriter.WriteEndElement();
        // Stop writing the XML document 
        textWriter.WriteEndDocument();
        // Close document 
        textWriter.Close();
    }

    // Method that will be started for each record in you dataflow 
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Row type to get the value of a column 
        var properties = Row.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);;

        String columnValue = "";
        // Create row element:  
        textWriter.WriteStartElement("ROW");
        // Loop through all columns and create a column element: valuevalue 

        foreach (var property in properties.Where(p => !p.Name.EndsWith("_IsNull", StringComparison.OrdinalIgnoreCase)))
        {
            // Use the SSIS column name as element name:  
            textWriter.WriteStartElement(property.Name);

            // Get column value, but that method will fail if empty
            // so first check if column value is null
            if (!CheckNull(Row, properties, property.Name))
            {
                // Get column value
                columnValue = property.GetValue(Row, null).ToString();
            }
            else
            {
                // Default value for null values: "null", "" or null 
                columnValue = "";
            }
            
            // write column value, but first remove forbidden chars
            textWriter.WriteString(removeForbiddenXmlChars(columnValue)); 

            // Close column element:  
            textWriter.WriteEndElement(); 
        }
        // Close row element:  
        textWriter.WriteEndElement();
        // Output the number of processed rows. 103 = RowsWritten 
        this.ComponentMetaData.IncrementPipelinePerfCounter(103, 1);
    }

    // Remove forbidden chars that could damage your XML document 
    private string removeForbiddenXmlChars(string columnValue)
    {
        return columnValue.Replace("&", "&").Replace("<", "<").Replace(">", ">");
    }

    // Method that checks wether a column value is empty.
    // It uses the standard boolean [columnName]_IsNull.
    private bool CheckNull(Input0Buffer Row, PropertyInfo[] properties, string propertyName)
    {
        var property = properties.Where(p => p.Name.Equals(propertyName + "_IsNull", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();

        if (property != null)
        {
            if ((bool)property.GetValue(Row, null))
            {
                return true;
            }
        }
        return false;
    }
}

And the VB.net code

' VB.Net code
' Flexible script that creates a XML document 
' using the SSIS columns as nodes.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Reflection   ' Added 
Imports System.Xml          ' Added 
Imports System.Linq         ' Added
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    ' Variable that contains the XML document 
    Dim textWriter As XmlTextWriter

    ' Start of XML document 
    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        ' Create a new XML document and use the filepath in the connection as XML-file 
        textWriter = New XmlTextWriter(Me.Connections.xmldocument.ConnectionString.ToString(), System.Text.Encoding.Default)
        'Start writing the XML document: 
        textWriter.WriteStartDocument()
        'Create root element  
        textWriter.WriteStartElement("ROOT")
    End Sub

    ' Close of XML document
    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        'Close root element:  
        textWriter.WriteEndElement()
        'Stop writing the XML document 
        textWriter.WriteEndDocument()
        'Close document 
        textWriter.Close()
    End Sub

    ' Method that will be started for each record in you dataflow
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Row type to get the value of a column 
        Dim properties = Row.[GetType]().GetProperties(BindingFlags.Instance Or BindingFlags.[Public])

        Dim columnValue As [String] = ""
        ' Create row element:  
        textWriter.WriteStartElement("ROW")
        ' Loop through all columns and create a column element: valuevalue 

        For Each [property] As Object In properties.Where(Function(p) Not p.Name.EndsWith("_IsNull", StringComparison.OrdinalIgnoreCase))
            ' Use the SSIS column name as element name:  
            textWriter.WriteStartElement([property].Name)

            ' Get column value, but that method will fail if empty
            ' so first check if column value is null
            If Not CheckNull(Row, properties, [property].Name) Then
                ' Get column value
                columnValue = [property].GetValue(Row, Nothing).ToString()
            Else
                ' Default value for null values: "null", "" or null 
                columnValue = ""
            End If

            ' write column value, but first remove forbidden chars
            textWriter.WriteString(removeForbiddenXmlChars(columnValue))

            ' Close column element:  
            textWriter.WriteEndElement()
        Next
        ' Close row element:  
        textWriter.WriteEndElement()
        ' Output the number of processed rows. 103 = RowsWritten 
        Me.ComponentMetaData.IncrementPipelinePerfCounter(103, 1)
    End Sub

    'Remove forbidden chars that could damage your XML document 
    Private Function removeForbiddenXmlChars(ByVal columnValue As String) As String
        removeForbiddenXmlChars = columnValue.Replace("&", "&").Replace("<", "<").Replace(">", ">")
    End Function

    ' Method that checks wether a column value is empty.
    ' It uses the standard boolean [columnName]_IsNull.
    Private Function CheckNull(ByVal Row As Input0Buffer, ByVal properties As PropertyInfo(), ByVal propertyName As String) As Boolean
        Dim [property] = properties.Where(Function(p) p.Name.Equals(propertyName & "_IsNull", StringComparison.OrdinalIgnoreCase)).FirstOrDefault()

        If [property] IsNot Nothing Then
            If CBool([property].GetValue(Row, Nothing)) Then
                Return True
            End If
        End If
        Return False
    End Function
End Class

Do 'something' for all columns in your dataflow

Case
If you have a lot columns that all need to be adjusted with the same function (let's say uppercase). You could use the Derived Column component or the Character Map component to adjust all columns one by one. The Script Component can make your life easier.

Starting point

Col1	Col2	Col3
test	Test	TEST
bla	Bla	BLA
xxx	Xxx	XXX

Desired situation

Col1	Col2	Col3
TEST	TEST	TEST
BLA	BLA	BLA
XXX	XXX	XXX

Solution
1) Add a script component to you dataflow.

Script component (transformation)

2) Select all columns (you need) and select ReadWrite.

ReadWrite Input columns

3) Now the script (I used VB.net in this example). SSIS will generate some default methods. We will only use Input0_ProcessInputRow. You can remove the other methods. The script loops via reflection through all string fields and will uppercase the value. You can adjust the second method to suit your needs.

' This script adjusts the value of all string fields
Imports System
Imports System.Data
Imports System.Math
Imports System.Reflection ' Added
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<microsoft .sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute=".sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute"> _
<clscompliant false="false"> _
Public Class ScriptMain
    Inherits UserComponent

    ' Method that will be started for each record in you dataflow 
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Use Reflection to loop through all the properties of Row:
        ' Example:
        ' Row.Field1            (String)
        ' Row.Field1_IsNull     (Boolean)
        ' Row.Field2            (String)
        ' Row.Field2_IsNull     (Boolean)
        Dim p As PropertyInfo
        For Each p In Row.GetType().GetProperties()
            ' Do something for all string properties: Row.Field1, Row.Field2, etc.
            If p.PropertyType Is GetType(String) Then
                ' Use a method to set the value of each String type property
                ' Make sure the length of the new value doesn't exceed the column size
                p.SetValue(Row, DoSomething(p.GetValue(Row, Nothing).ToString()), Nothing)
            End If
        Next
    End Sub

    ' New function that you can adjust to suit your needs
    Public Function DoSomething(ByVal ValueOfProperty As String) As String
        ' Uppercase the value
        ValueOfProperty = ValueOfProperty.ToUpper()
        Return ValueOfProperty
    End Function

End Class

or in C#

// This script adjusts the value of all string fields
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;            // Added

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent
{
    //  Method that will be started for each record in you dataflow 
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Use Reflection to loop through all the properties of Row: 
        // Example: 
        // Row.Field1 (String) 
        // Row.Field1_IsNull (Boolean) 
        // Row.Field2 (String) 
        // Row.Field2_IsNull (Boolean) 
        foreach (PropertyInfo p in Row.GetType().GetProperties())
        {
            // Do something for all string properties: Row.Field1, Row.Field2, etc. 
            if (object.ReferenceEquals(p.PropertyType, typeof(string)))
            {
                // Use a method to set the value of each String type property 
                // Make sure the length of the new value doesn't exceed the column size 
                p.SetValue(Row, DoSomething(p.GetValue(Row, null).ToString()), null);
            }
        }
    }
 
    // New function that you can adjust to suit your needs 
    public string DoSomething(string ValueOfProperty)
    {
        // Uppercase the value 
        ValueOfProperty = ValueOfProperty.ToUpper();
        return ValueOfProperty;
    }
}

4) Now add a target and run the package to see the result:

Added two data viewers to see the result.