Intended audience
This article assumes that you have at least a basic understanding of SSIS, and the Data Flow Task in particular. C# knowledge is an advantage but not required.
Introduction
If you ever used the T-SQL windowing function ROW_NUMBER() OVER (PARTITION BY ..) to find duplicates in a query result, this post might be of interest for you.
Because not all sources having potential duplicate entries (e.g. business keys) come from a SQL Server database. If it is a csv- or Excel file ROW_NUMBER will not automaticly be your friend (it might be when you use OPENROWSET, but I think that is a rather complicated solution).
C# script components are extremely flexible yet fairly easy to understand, develop and maintain.
This is why this is my preferred solution to tackle the problem above.
Let’s begin!
How to detect duplicates in a Data Flow Task using a C# Script component
First an overview of the end result (the Data Flow of the Data Flow Task). I will tell you step by step how to achieve this using SSIS 2014. I did not make a print screen of the Control Flow because it only contains one Data Flow Task.
Please note that I enabled two Data Viewers on both outputs of the Conditional Split component, so you can see the data that flows through (screenshot later).
For the demo I will use the following components in a dataflow. To solve your problem just pick out the things you need.
- An OLE DB Source
- A script component (the most interesting part!)
- An (optional) conditional split
- Two OLE DB Destinations: one for the first occurrences of each business key, and one for the duplicates.
An OLE DB Source
My example is based on Advertureworks. I select customers from the [AdventureWorksDW] database, and make sure there are duplicates by using UNION ALL in the SQL statement.
In the real world it would be more likely that your source would not be a SQL Server query, but for instance a csv- or Excel file.
SELECT
[CustomerKey], [CustomerAlternateKey] AS [CustomerBusinessKey],
[Title], [FirstName], [MiddleName], [LastName],
[NameStyle], [BirthDate], [MaritalStatus], [Suffix],
[Gender], [EmailAddress], [YearlyIncome]
FROM
[dbo].[DimCustomer]
UNION ALL
SELECT
[CustomerKey], [CustomerAlternateKey] AS [CustomerBusinessKey],
[Title], [FirstName], [MiddleName], [LastName],
[NameStyle], [BirthDate], [MaritalStatus], [Suffix],
[Gender], [EmailAddress], [YearlyIncome]
FROM
[dbo].[DimCustomer]
UNION ALL
SELECT
[CustomerKey], [CustomerAlternateKey] AS [CustomerBusinessKey],
[Title], [FirstName], [MiddleName], [LastName],
[NameStyle], [BirthDate], [MaritalStatus], [Suffix],
[Gender], [EmailAddress], [YearlyIncome]
FROM
[dbo].[DimCustomer]
WHERE CustomerKey % 2 = 0
SQL Statement for the OLE DB Source
A script component (the most interesting part!)
From the SSIS Toolbox drag a Script Component to the Data flow surface. In the “Select Script Component Type” choose “Transformation”.
To use a column value in the script, you have to define it as an input column. Select the column you want to check for duplicate values with Usage Type ReadOnly. In the demo this is the CustomerBusinessKey column.
We also need a new column to store the occurrence number for the CustomerBusinessKey. To do this, in the Script Transformation Editor, Select Inputs and Outputs on the left side, then unfold Output 0 and Output Columns, respectively.
Then click on the Add column button to add a column with DataType four-byte signed integer [DT_I4]. Give the new column a clear name. In the demo it is called CustomerBusinessKeyOccurrence.
After these preparations we can start writing the C# script. Press the Edit script … button to do so.
Adjust the script as follows (the changes are also marked inside red blocks in the picture below):
- In the Namespaces region, add:
using System.Collections.Generic;
- Above PreExecute(), add:
// The column to count duplicates for will be stored as Key (with string datatype) of the dictionary.
// The occurrence counter will be stored as Value of the dictionary (with int datatype).
private Dictionary<string, int> m_dictOccurrencesCount; - Inside PreExecute(), add:
// Initialize the dictionary, otherwise it will be null when accessed.
m_dictOccurrencesCount = new Dictionary<string, int>();
Script adjustments – part 1
Then adjust the script as follows:
- Inside Input0_ProcessInputRow(Input0Buffer Row), add:
// Count occurrences of CustomerBusinessKeys.
if (m_dictOccurrencesCount.ContainsKey(Row.CustomerBusinessKey))
{
// If the CustomerBusinessKey of the current row is already in the dictionary,
// increase the counter with 1:
m_dictOccurrencesCount[Row.CustomerBusinessKey]++;
// Also assign the new value to the CustomerBusinessKeyOccurrence column,
// so it can be used in the Data flow later.
Row.CustomerBusinessKeyOccurrence = m_dictOccurrencesCount[Row.CustomerBusinessKey];
}
else
{
// If the CustomerBusinessKey of the current row is not yet in the dictionary,
// add it, and set the counter to 1
m_dictOccurrencesCount.Add(Row.CustomerBusinessKey, 1);
// Also assign 1 to the CustomerBusinessKeyOccurrence column.
Row.CustomerBusinessKeyOccurrence = 1;
}
Script adjustments – part 2
When the script is ready, Choose Rebuild from the BUILD menu in the VstaProjects – Microsoft Visual Studio Window. Then close that Window. You return to the Script Transformation Editor, close it by pressing OK.
An (optional) conditional split
Optionally you can add a Conditional Split.
I use the conditional split to store the first occurrences for every CustomerBusinessKey in one table and all duplicates in another table.
Both tables are only to show you that the script task and conditional split do their jobs properly. In real projects, you might develop this differently, for example by storing only the first occurence.
Conditional Split Configuration
Two OLE DB Destinations: one for the first occurrences of each business key, and one for the duplicates
I basicly created a two temporary tables by composing a SELECT TOP 0 .. INTO statement from the OLE DB Source SQL Statement, because the result of the demo needs to flow to somewhere.
DROP TABLE [dbo].[DimCustomerFirstOne]
GO
SELECT TOP 0
[CustomerKey], [CustomerAlternateKey] + 'B' AS [CustomerBusinessKey],
[Title], [FirstName], [MiddleName], [LastName],
[NameStyle], [BirthDate], [MaritalStatus], [Suffix],
[Gender], [EmailAddress], [YearlyIncome], CONVERT(int, 0) AS [OccurrenceNo]
INTO [dbo].[DimCustomerFirstOne]
FROM
[dbo].[DimCustomer]
GO
IF OBJECT_ID('[dbo].[DimCustomerDuplicates]', 'U') IS NOT NULL
DROP TABLE [dbo].[DimCustomerDuplicates]
GO
SELECT TOP 0
[CustomerKey], [CustomerAlternateKey] + 'B' AS [CustomerBusinessKey],
[Title], [FirstName], [MiddleName], [LastName],
[NameStyle], [BirthDate], [MaritalStatus], [Suffix],
[Gender], [EmailAddress], [YearlyIncome], CONVERT(int, 0) AS [OccurrenceNo]
INTO [dbo].[DimCustomerDuplicates]
FROM
[dbo].[DimCustomer]
GO
OLE DB Destination OLE_DST FirstOne uses the [dbo].[DimCustomerFirstOne] table.
OLE DB Destination OLE_DST Duplicate uses the [dbo].[DimCustomerDuplicates] table.
Let it flow, let if flow, let it flow
Okay, time to test.
When data flows, the “First occurence” and “Duplicates” Data Viewers pop up. Then I can check that all the rows are divided well over both tables based on the value of column CustomerBusinessKeyOccurrence: exactly 1 or higher than 1.
When I run it with the Data Viewers disabled, the package execution finished literally in a split second. So the performance is excellent.
Test succeeded!
The Data flow in Debug Mode
Data Viewer for First occurrence flow: CustomerBusinessKeyOccurrence is 1 for all rows.
Data Viewer for Duplicates flow: CustomerBusinessKeyOccurrence is 2 or higher for all rows.
Other considerations
If multiple columns make a row unique concatenate all column values with a delimiter, and then add the concatenated result to the dictionary m_dictOccurrencesCount.
For instance you could check duplicates using the customer nameparts (firstname, middlename and lastname), the birthdate and the gender. This is a softer check than on a real business key, but sometimes you do not have that.
Of course, for this to work, you must add those columns as ReadOnly input columns to the script component.
You can create a concatenated value as follows:
Inside Input0_ProcessInputRow(Input0Buffer Row), start with:
string businessKey = string.Format(“{0}|{1}|{2}|{3:yyyyMMdd}|{4}”,
Row.FirstName, Row.MiddleName, Row.LastName, Row.BirthDate, Row.Gender).ToUpper();
The concatenated value is converted to uppercase, so that differences in lower- or uppercase lead to the same value.
Then use the businessKey variable (instead of Row.CustomerBusinessKey) in the if statement as shown above.
If the concatenated value becomes very long, you could calculate a hash value and add that to the dictionary instead (thanks for the suggestion, Joost). This would use less memory and might be faster, but the only way to find out if this is the case is testing .. testing ..
( I did not test this to make the demo not too complicated and because performance did not seem to be an issue ).
Thinking a bit further, you could use this concept also to calculate running totals.
Just configure the dictionary to hold a decimal instead of an int and you can calculate running totals for numeric values (e.g. sales figures) easily.
Conclusion / Wrap up
In SSIS you can use a Script Component within a Data Flow Task to detect duplicates in the source data.
This can be an alternative to the T-SQL ROW_NUMBER() function, when the source is not a SQL Server Database.
Credits
My thanks go to Joost van Rossum for reviewing the initial draft of this article. His input was very useful.
(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.
