CRM, SSIS & Integrations ...configuring CRM as a non-coder
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
CRM, SSIS & Integrations
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
  • Home
  • CRM
  • KingswaySoft
  • SSIS
  • Using the KingswaySoft Duplicate Detector

Using the KingswaySoft Duplicate Detector

October 25, 2016 Leave a Comment Written by Thomas

KingswaySoft recently released their version 1.0 of the Productivity Pack which has some awesome features – and I will focus on one in particular in this blog post. The Duplicate Detector. I’ve already used this tool on a few customers, and it makes finding potential duplicates in data a lot easier.

I’ve used it in two different scenarioes:

  1. Scanning for existing duplicates in a CRM system
  2. Scanning for potential duplicates from import data sheet, where I compared the data to the CRM system.

In the first scenario, the setup is rather straight forward. You need a source, the duplicate detector component and a result destination (I’ve prefer using an Excel sheet for this).

duplicate_1

The duplicate detection is setup in the fasion, where you select the column(s) you wish to match on. You can then select the matching type and the precision (1 to 100%). Most of the match types are self-explanatory, but I would suggest you try the different ones, and see which one works for your scenario. If you are look for account name duplicates, then I would ofcause suggest using the Company Name Match.

duplicate_4

The most important part of the duplicate detection is however the results, and how those are presented. The way KingswaySofts duplicate detector does it, is with a Group Code. If I have two accounts called AA and A.a, those would be grouped with the same GroupCode – fx 154566121. This is also the matching criteria, which you can sort the duplicates on.

GroupCode Status AccountId Name
100536828 Active {DCE400A1-8335-E611-80E1-5065F38ADA41} A.a
100536828 Active {F98976AB-3594-E611-80F1-5065F38A5A21} AA
1005414176 Active {0A5ED241-8335-E611-80E1-5065F38ADA41} BOSS GmbH
1005414176 Active {83BF2D9C-6C97-E611-80EE-5065F38ADAE1} GmbH BOSS
1041043548 Active {77F64167-8235-E611-80E1-5065F38ADA41} Bbas
1041043548 Active {C700A7AF-7397-E611-80F0-5065F38B4681} BBAS SERVICES

What you do with the results, is up to you. It can be deleted, merged, or something completely different.

In the second scenario, the data are not yet in CRM, but we still want to compare the data to the data in CRM. I will need two sources, and a Union all to combine them. I mash all the data through the Duplicate detector, to get the results I need.

duplicate_5

When using this method, though, I will get alot more rows than I need, since the CRM source checks the whole CRM account database – but I only want the ones connected to my import data.

duplicate_6

To get those results, I have anoter step in my job, which filters the duplicates:

SELECT A1.Name, A1.AccountId, A1.GroupCode
FROM [Duplicates_Full$] as A1
WHERE A1.GroupCode IN 
(SELECT A2.GroupCode FROM [Duplicates_Full$] as A2 WHERE A2.AccountId IS NULL) 
AND 
(SELECT COUNT (*) FROM [Duplicates_Full$] A3 WHERE A3.GroupCode = A1.GroupCode GROUP BY A3.GroupCode) > 1

 

With that query in the Excel source, I can filter all those duplicates which have at least one row without an AccountId (are from the import data).

You can download the KingswaySoft duplicate detector and all the other wonderful toolkits in their productivitypack on their website here.

CRM, KingswaySoft, SSIS
CRM 2015, CRM 2016, CRM Online, Data, Excel, How To, KingswaySoft, News, SQL, SSIS
Migration Starter Pack for CRM Online & On-Premise
Millions of credential validations on the Active Directory from SSIS

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

The author

KingswaySoft MVP & Microsoft certified professional

My name is Thomas and I am a consultant at a company called netcompany A/S in Denmark. My primary focus is working with SSIS and KingswaySoft, where I perform integrations and datatranformations.

Click for more info

Recent Posts

  • Difference between two dates (SQL, SSIS and JavaScript)
  • Easy way to fetch row count for all tables in a SQL database
  • Create Application user in Azure for use in Dynamics 365
  • SSIS Basics: Merge join
  • Open dashboards in a new window

Useful information

  • Installation guide to KingswaySoft
  • Software download list
  • CRM Object Type Codes
  • CRM State & Status codes
  • Script Repository

Archives

  • May 2020 (1)
  • February 2020 (1)
  • January 2020 (2)
  • August 2019 (1)
  • May 2019 (1)
  • February 2019 (2)
  • January 2019 (2)
  • November 2018 (1)
  • September 2018 (1)
  • August 2018 (3)
  • May 2018 (4)
  • December 2017 (2)
  • August 2017 (1)
  • February 2017 (1)
  • November 2016 (4)
  • October 2016 (1)
  • September 2016 (2)
  • August 2016 (5)
  • June 2016 (4)
  • May 2016 (3)

Useful blogs/links

Henrik Jensen, my good friend and colleague, has a blog regarding EVERYTHING Dynamics crm. He's got 50+ certifications, and he is a force to be reckoned with. Link to his blog (danish): crmblog.dk

KingswaySoft have developed an outstanding piece of software for datamanagement to and from CRM, and I can recommend this toolkit for nearly every situation. Link to their website: kingswaysoft.com

Tag Cloud

Azure Bug CRM 2015 CRM 2016 CRM Online Data Dynamics365 ERP Excel How To JavaScript KingswaySoft Log News Office Outlook Personal Solution SQL SSIS Visual Studio

Categories

  • Azure (2)
  • Clubtimiser A/S (1)
  • CRM (30)
  • ERP (2)
  • How-to (14)
  • JavaScript (3)
  • Office (3)
  • Personal (4)
  • SQL (2)
  • Visual Studio (23)
    • SSIS (23)
      • KingswaySoft (14)

Disclaimer

The information given on the website is tested on a mixture of live production environments and test environments, and therefore I cannot guarantee my information will work for you, if you attempt to follow my steps.
If you experience problems, please feel free to contact me.

@June 7th 2016