No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Who is a Microsoft Regional Director?
An Interview Question
Ask a Question
C, C++, MFC
Universal Windows Platform
Algorithms in C#
Coding Best Practices
Databases & DBA
Learn C# Corner
Reports using C#
Internet & Web
Internet of Things
Request a new Category
SQL Server Integration Services (SSIS) - Fuzzy Grouping Transformation in SSIS
In this article we are going to see how to use the Fuzzy Grouping task. This task is mainly used to group some similar data in a row and cleaning the duplicates to maintain a standard of the table. This task requires a connection to the SQL database that the transformation algorithm requires to. Let's jump start to the section on how to do that using a sample package.
You can look into my series of article on SSIS at the url -
Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Fuzzy Grouping control. Once you open the project just drag and drop the Fuzzy Grouping control and a source provider as shown in the below image.
Now I configured the OELDB source by selecting Order tables from Northwind database as shown in the below screen.
Now we need to configure the Fuzzy Grouping; double-click on the task and it will open the window as shown in the below screen:
We need to do the configuration as shown in the below screen. Now we will see how the fuzzy grouping transformation works. Go to the Columns tab and select the row which we need to exact do the full search of the reduplicating and applying the fuzzy algorithm.
CustomerID is the value for which we are going to do a fuzzy grouping using the in-built algorithm. Here we have various options to search for the conditions.
Match Type has the 2 values EXACT and FUZZY. Exact does the exact match for the specified column and gives the result only if it matches the exact value. Fuzzy does a similar search and checks for a specified value and uses it to do the transformation (for example a column employee name has Karthik A and Karthik B if the type is Fuzzy then it takes this value into account where in Exact it will not take since last name A and B are not exact).
How exactly the algorithm works is as follows:
Step 1 â€“ It transforms and loads the data to a temporary table
Step 2 â€“ Fuzzy algorithm is applied and searches for the matches
Step 3 â€“ For each row the transformation conditions checks for the match above the threshold and created an exact match
Step 4 â€“ Applies the resulted output to the packages to proceed further
The columns available for the fuzzy grouping logic are as follows:
Input Column â€“ Selected columns
Output Alias â€“ This values comes as output for the fuzzy inputs
Group Output Alias â€“ This values holds the best value match
Match Type â€“ Exact or Fuzzy selection
Minimum Similarity â€“ Has the minimum similarity value
Similarity Output Alias â€“ Has the similarity score for that column
Numerals â€“ Handles the matched data in number
Comparison Flags â€“ Checks for the comparison string handling
Now move to the next tab (Advanced) as shown in the below image:
Here we update the global values used across the package to handle the fuzzy transformation applied here. The main feature here we need to look for is the similarity threshold where we need to specify the minimum threshold for match to be accepted in the transformation.
So in this article we have seen how to use the Fuzzy Grouping transformation task and the key configurations used for this handy task.
Fuzzy Grouping Transformation in SSIS
Grouping Transformation in SSIS
SQL Server Integration Services
Transformation in SSIS
Factory Method Design Pattern In C#
ASP.NET MVC - Passing Data From Controller To View
SPA Using Angular 2, ASP.NET Core 1.1 And Entity Framework Core - Part One
Insert Data By Stored Procedure In MVC 5.0 With Data First Approach
Steps To Perform CRUD Operations Using AngularJS And Stored Procedure In An ASP.NET MVC
$q Service In AngularJS
CRUD Operation Using Single Stored Procedure In ASP.NET MVC Of Real-Time Project
Overview And Getting Started With AngularJS In ASP.NET Using Visual Studio 2017
Aggregate Function With LINQ In C#
File Providers In ASP.NET Core