SSIS and CRM Series, Part 3 - Use SSIS to Perform Batch Update or Delete in CRM

This is part 3 of Blog Series - Better Together, SSIS and Microsoft Dynamics CRM.

Scenario Summary

The terminology of Batch that we use in this blog post might be slightly different from the same term used in ETL job, which is usually referred as scheduled batch job.

In this blog post, we are trying to use SSIS to do some ad-hoc batch process in order to fix the CRM data that might have problems.

Let's visit two possible scenarios that you might run into.

The first possible scenario is, your CRM system has been running for a few years. Your organization started the CRM system with loose control of data entry in the beginning, so your system is stuffed with all kind of crappy data, including incorrectly formatted data (such as phone, email address, etc.). As time goes, the management team starts to grow their expectation about the data quality in the CRM system which is essentially the vehicle that drives the business. Let's say, you have got hundreds of thousands of account records (or even more) in the system that you need to fix the format of a phone number field. Without a proper tool, this is going to be a big undertake considering the number of records that you need to fix.

Another possible scenario is, you (or someone else in your organization) have entered some significant amount of data into CRM system, and you come to the situation that they are no longer needed, and you need to find a way to delete them all from the system quickly. You would probably think this is an easy job, since you can do bulk delete in CRM.  However, when you need to deal with more than a few thousands of records, you will soon realize that this is not a fun job at all. It requires a lot of mouse clicks to get through all pages and click Delete ribbon button and wait for each record to be deleted from the system. Your time can be better used for something else as a professional developer!

With the availability of SSIS Integration Toolkit, let's see how you can let the toolkit do all the heavy lifting for you so that you can get the job done quickly.

Let's get started.

Prerequisites

You need to have a system that has the following components installed.

• Business Intelligence Development Studio component from either SQL Server 2005,  SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012
• SSIS Integration Toolkit for Microsoft Dynamics CRM
• Windows Identity Foundation

Developing SSIS Data Flow

• First, you would launch Business Intelligence Development Studio to create a new SSIS project, and then create a new SSIS package within the project. 
• You would need to create one Microsoft Dynamics CRM connection manager that connect to your CRM system.
  
• In the SSIS package, create a new data flow. 
• Within the data flow, create a new CRM Source Component that reads the CRM data that you need to process by choosing the concerned entity (or use FetchXML option if you need to apply filtering when retrieving data). 
• In the data flow, create a new CRM Destination Component, and connect it to the CRM Source Component that we have just created. The CRM destination component will be used to write data back to or delete CRM records from the same CRM server. So in its destination component, we simply choose the same Connection Manager, and the same CRM entity that you have chosen in the source component, select Update or Delete as the Action option depending on what you are trying to achieve, and leave all the rest options as default. 
• In the case when we want to use batch update to fix data issues, we would add SSIS transformation component(s) between the CRM source component and CRM destination component. Those in-between transform components implement the business logics with regard to how you want to fix the data. The following is a simple sample data flow. In the data flow, we read CRM account records from the system, and do some formatting to the record's telephone1 field using SSIS Derived Column transformation component, and then update the CRM account records back to the original CRM server.
  
• To demonstrate the batch delete functionality using SSIS, we use FetchXML query in CRM source component to retrieve all accounts that have name started with "Test", our intention is to delete all of them using CRM destination component.
  
• After we have finished developing the data flow, we can start to run the data flow to perform the batch update or delete in CRM system. The following screen shows the result for my SSIS batch delete data flow, in which case I have deleted 1,604 account records. The data flow takes roughly a couple of minutes to finish for the amount of records that I have.
  

Summary Notes

• You can use SSIS to perform ad-hoc batch update or delete for Microsoft Dynamics CRM in a quick fashion. SSIS is a great tool that can be helpful in many scenarios, this blog post has just illustrated the mentioned two scenarios. If you know SSIS well enough, you can do a lot more by using the tool. 
• The key of doing the batch update or delete is to use the same connection manager and same CRM entity in both SSIS source component and destination component. 
• In the sample batch update data flow shown in this blog post, I have used a very simple business logic. SSIS is capable to perform complex data transformation and manipulation using various transformation components. 
• I have skipped some basic details in this blog post about how you create SSIS project, how you add connection managers, and how you add CRM components to your SSIS toolbox. All such details can be found in part 1 of this blog series or the product's help manual page. 
• SSIS Integration Toolkit is a commercial software, and I work for KingswaySoft, the provider of the tool. 

Thanks for reading, hope this helps.