Saturday, May 19, 2012

SSIS and CRM Series, Part 4 - Use SSIS to Implement Data Quality Solution for Microsoft Dynamics CRM

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

Scenario Summary

There is no doubt that data quality is important in business applications. High-quality data is critical to the efficiency of business operations.

Using Microsoft Dynamics CRM as your business application platform, it is a challenge to implement data quality solution that's both efficient and cost-effective.

In SQL Server 2012, Microsoft introduced Data Quality Services (DQS) which enables database or IT professionals to manage business data quality and ensure the data is suited for its business usage.

In this blog post, we are going to leverage Microsoft Data Quality Services (DQS) to implement data quality solutions for Microsoft Dynamics CRM applications using  SSIS Integration Toolkit.

For illustration purpose, we have the following CRM account records in the system that we want to process. You may have noticed that the State/Province field is filled with data of many different formats, which is the problem we want to fix, our goal is to update the field by converting its value to the standard two-letter state code, such as CA for California, WA for Washington, and so on. This is just a very simple sample for demo purpose, your business requirement of data quality solution could be more complex.

Prerequisites

You need to have a system that has the following components installed.
NOTE:  It is not necessarily the case that DQS is the only way to implement data quality solution. Without SQL Server 2012, you can still use a combination of SSIS transformation components to implement data quality solution in SSIS. However, using Microsoft Data Quality Services provides a number of benefits, which is the reason that we are using it in this blog post. 

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. The connection manager is going to be used as both source and target, so that we read data from the CRM data, process it through DQS, and write it back to the same CRM server.
  • In the SSIS package, create a new data flow. 
  • Within the data flow, create a new CRM Source Component that reads the CRM records for processing. As shown in the screen shots below, we selected account entity, and chose "accountid", "address1_stateorprovince" fields as the output columns of the source component. If you want to process a subset of CRM entity records, you can choose FetchXML option by providing a FetchXML query in order to retrieve the only records that you are interested.


  • Next thing to do is to drag Data Cleansing component from Visual Studio toolbox to the design surface of SSIS data flow, and connect it to the previously created CRM source component. Double click the Data Cleansing  component to open its editor window.
  • Click "New" button to create a new Data Cleasing Connection Manager, and enter the name of server that has DQS Service installed. Click "OK" button to return to the editor window.
     
  • Select Data Quality Knowledge Base that you have. After you have a selected knowledge base, you will be shown a list of available domains. 
    Note: Creating and managing DQS knowledge base and domains are out of scope for this blog post. You may check SQL server documentation for further details.
  • Go to Mapping page, and select the field that we need to process in the top grid, then select a domain for the data cleansing process.

    Note the value in Output Alias column in the bottom grid, it will be used by DQS cleansing component to output the corrected data for downstream pipeline components.
  • Click OK button to finish configuring DQS Cleansing component. 
  • Create a new CRM destination component and connect it to the DQS Cleansing component. Double click the destination component to bring up its editor window. 
  • Select the same CRM connection manager, and choose Update as Action option, select the same entity that you have chosen in CRM source component.
  • Navigate to the destination component's Columns page, and make sure to map the Output column from DQS Cleansing component to the original CRM field so that we write the processed data back to CRM.
  • After the data flow is finished, you can start to run it. If everything goes right, you can see a screen like the following one.
  • It is now time to verify the result of DQS cleansing. The following screen is the result of the process. As you can see, all states have been updated to their two-letter standard code.

Summary Notes

  • This blog post provides a quick tutorial about how to implement data quality solution for Microsoft Dynamics CRM using SQL Server DQS Cleansing component andSSIS Integration Toolkit
  • You can run the developed SSIS package at scheduled time using SQL Server scheduling functionality, so that you can process data to enforce data rules through DQS on a schedule-basis. It can be daily, or every few hours, maybe even every few minutes depending on your business requirement. 
  • You have been shown how to correct CRM data within the system using DQS Cleansing component, the same technique can be used to implement data quality solution when you load external data  into Microsoft Dynamics CRM. You just need to add DQS Cleansing component before CRM destination component as we have demonstrated.
  • DQS is a very broad topic, we only scratched the surface of this topic in this blog post. For more information about DQS, you should check SQL Server documentation. 
  • As mentioned in the post, it is not necessarily the case that SQL Server 2012 DQS is the only way to implement data quality solution. However, using Microsoft Data Quality Services provides a number of benefits, such as centralized knowledge base, flexible domain management, and consistent data quality framework, etc. 
  • SSIS Integration Toolkit is a commercial software, and I work for KingswaySoft, the provider of the tool. 
Thanks for reading, hope this helps.

Friday, May 18, 2012

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.

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. 

Thursday, May 17, 2012

SSIS and CRM Series, Part 2 - Use SSIS to Copy CRM Configuration Data from One Environment to Another

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

Scenario Summary

In CRM projects, we often rely on configuration data to implement business logics. One of such scenarios is CRM workflow. It is very common that we use lookup reference in CRM workflows to implement business rules.

The following is a sample workflow that fires when a new CRM account is created. The workflow will create a task record when the CRM account record belongs to country USA (Country in this case is a custom entity, which has a 1:N relationship to CRM account entity).
All is good. We have the workflow developed in our development (DEV) environment. After your verification is done in DEV, you will start to promote the changes to your testing or production environment. That should be an easy job with the help of CRM solution framework, you would say. However, this might not be the case exactly. After your solution is imported into your target environment, you will then realize that your workflow isn't working. You are getting an error message saying "The process includes an invalid reference", shown as the following screen.  
As indicated by the error message, the problem is rather simple, your workflow is using a lookup reference, but the referenced CRM record (the USA country record in our case) doesn't exist in the target system.

If you have not worked long enough with Microsoft Dynamics CRM, you might be wondering to just simply create the CRM record(s) in the target system from CRM user interface using the same values that you have from the source system, and expect the workflow to work. You will soon realize that this will actually not work, CRM workflow won't use the record(s) that you have manually created. The reason is, as far as CRM reference goes, CRM only recognize a record's ID in GUID format, not the primary field value or values from any other fields.

So, how can we fix this problem?

The answer is quite simple too, you basically have two solutions. The first solution is to manually fix the reference in the target system one by one, by opening each workflow in its designer. The second option is to find a way to copy (migrate, transfer, or whichever terminology that you may think appropriate) such CRM configuration data to the target system by the same IDs along with the values from other fields. The choice would be obvious, the second one would be a much better option if there is an easy to do so.

Assuming that you have decided to go with the second option, you would then be looking for tools that can help copy configuration data from one environment to another. There are a number of ways to do this, you can possibly use CRM native tools including its exporting tool and CRM Data Import Wizard to first export data from source system and then import to the target system. However, there are a number of limitations if you use CRM export and import tools, and there is almost no way to manage incremental changes. You would soon started blame yourself if you decided to go this path. Alternatively, you can write C# (or VB) code using CRM SDK to export data from source system and import to the target one. This is not an easy job either, as it takes effort to write code, especially it becomes quite challenging when you need to make it generic enough in order for the tool to support more than one entity. My previous preference was CRM Configuration Data Utility, which is a utility built for CRM4, and it is a very handy tool to handle this situation. I have used the utility extensively in my previous CRM projects.

With the availability of SSIS Integration Toolkit, we can be even more productive when it comes to this business scenario.

Let's get into action to see how this can be done using SSIS Integration Toolkit.

Prerequisites

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

Develop 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 two Microsoft Dynamics CRM connection managers that connect to your source system and target system.
  • In the SSIS package, create a new data flow. 
  • Within the data flow, create a new CRM Source Component that reads data from the source system by choosing the entity that you want to copy data from. In our case, the entity we are working with is called new_country, your entity might be something different. 
  • 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 to the target system. In the destination component, you would choose the same CRM entity as the source component, select Upsert as the Action option, and Primary Key as Upsert Matching Criteria option. Also make sure to select "Remove Unresolvable Reference" option.
  • After you have finished developing the data flow (which should only take you a few minutes if you are not new to SSIS), you can start to run the data flow to copy data from your source system to target system (As shown below, I have copies 273 records with one single click).

Some Final Notes

  • The data flow can be reused, so whenever you have made any further changes in your source system, you can simply fire the SSIS package and run it again, your target system will have all the latest configuration data. 
  • In this blog post, I have shown you one typical problem that you need to deal with when working with Microsoft Dynamics CRM. SSIS Integration Toolkit is not limited to any particular entity, you can copy data from one environment to another, for any entity that can be created and updated by CRM SDK through its web service interface. 
  • 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 product, and I work for KingswaySoft, the provider of the tool. 
Hope this helps. 

Wednesday, May 09, 2012

SSIS and CRM Series, Part 1 - Use SSIS to Run Scheduled Jobs for Microsoft Dynamics CRM

In CRM projects, we often have the requirements to run scheduled jobs, on either daily, monthly basis (or maybe even every few minutes), to implement certain business logics. For instance, one of the typical scenarios is a business entity has an expiry date, and you want to deactivate all records that have passed the expiry date in the system. The challenge with Microsof Dynamics CRM is, there is currently no out-of-box support of scheduled jobs.

With the availability of SSIS Integration Toolkit for Microsoft Dynamics CRM, you can leverage SQL Server scheduling capability to implement this kind of scheduled jobs very quickly.

In this blog post, I will talk you through the process of implementing a scheduled SSIS job that runs against Microsoft Dynamics CRM. The business scenario of the implementation is, we have a CRM entity called Legislation, which has a physical name of new_legislation (This is just a make-up sample, I couldn't find a better sample at this moment) in the system. Legislation entity has a field called Expiry Date, with new_expirydate as its physical name. What we try to achieve in this blog post is, the SSIS job runs once a day at off-peak hour to deactivate all Legislation records in the system that has an Expiry Date of today.

Let's get started.

Creating SSIS Project and Package

  1. First of all, we need to ensure we have an SSIS development environment with SSIS Integration Toolkit for Microsoft Dynamics CRM installed. The SSIS development environment could be a SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 development machine that has Business Intelligence Development Studio (BIDS) installed. 
  2. We would then create an SSIS project after we have launched Business Intelligence Development Studio (or Visual Studio if SQL Server 2012 is used).
  3. In the SSIS project, we will need to add a new package by selecting "New SSIS Package" option after right-clicking SSIS Packages in Visual Studio Solution Explorer.

Creating CRM Connection Manager and a SSIS Data Flow Task

After we have created a SSIS package, we would then need to create a connection manager which facilitates the connection with Microsoft Dynamics CRM server.
  1. Within the SSIS Package that we created above, right-click in Connection Manager area, and choose "New Connection".
     
  2. We will be then prompted to select a connection manager from a list of all available connection managers in the system, including those provided by Microsoft and third-party software vendors. Choose "DynamicsCRM" from the list.
  3. We would then fill in the necessary information in order to be able to connect to our CRM server.

     

Creating a Data Flow Task

Within SSIS package, we can see four tabs. Control Flow and Data Flow are two of them where we would spend most of time when developing our ETL jobs (CRM scheduled jobs in our case here). As indicated by the names, SSIS Control Flow manages and controls the ETL tasks within the SSIS package, which can be made of various data flow tasks and some other ETL tasks. Within Data Flow task, we can design the flow of our data from one or more data sources to a destination.

Creating a data flow task is fairly simple, we can drag Data Flow Task from toolbox and drop it to the design surface of SSIS Control Flow. 

Adding a CRM Source Component

After we have created a data flow task, we would then double-click the task to switch to its design surface, in which case, we will be switched to Data Flow view. Now we need to create a CRM source component within the data flow task so that we can read records from CRM system that we need to perform the scheduled manipulation. 
  1. In order to add CRM source component to data flow, we need first add it to SSIS toolbox. To do so, we right-click on the toolbox area to bring up the context menu, where we select "Choose Items..." option as shown below.
  2. We would now be presented with a window called "Choose Toolbox Items", switch to "SSIS Data Flow Items" tab, and find Dynamics CRM Destination and Dynamics CRM Source components from the list, and make sure to check both of them (Dynamics CRM Destination component will be used next).
  3. Then we can add a Dynamics CRM Source component by dragging it from SSIS toolbox and dropping it to the data flow design surface. 
  4. In CRM Source Component, we need to choose a CRM connection manager that we have previously created. Also we would need to specify how we read data from CRM. We can choose to read data from a CRM entity, or we can provide a FetchXML query to read data from CRM. In our case, we will use FetchXML option so that we can read the only CRM records that we need to process.
In the above sample, we have tried to use a simple FetchXML query, which reads all CRM Legislation records that are active (statecode = 0) and have an Expiry Date of today.

SSIS Integration Toolkit for Microsoft Dynamics CRM supports any complex FetchXML query, and also you can use SSIS variables to parameterize your query, which gives you all the flexibility. 

Adding Processing Logic Using SSIS Transformation Components

After we have created the CRM source component, the next thing we need to do is to make changes to the CRM records in order to deactivate them. SSIS provides a number of transformation components that help transform and manipulate data. In our case, we will use Derived Columns transformation component to achieve our purpose. 
  1. Drag Derived Column component from SSIS toolbox and drop it to Data Flow design surface.
  2. Connect the Derived Column component to the CRM source component we previously created in the same data flow task.
  3. Double click the Derived Column component to open its editing window.
  4. In Derived Column Transformation Editor window, choose "Replace 'statecode'" in the grid, and enter 1 as expression (for most CRM entities, setting statecode to 1 is to deactivate the record). The component should look like the following screen.
After this step, we will have a data flow like the following screen. 
As mentioned, SSIS has a lot more transformation components that can be used to implement different business logics. We have tried to keep the sample simple. Even the Derived Column transformation component can be used in many different ways. 

Adding a CRM Destination Component

The final thing to do to finish the data flow development is to add a CRM destination component to the data flow. 
  1. Drag Dynamics CRM Destination component from SSIS toolbox and drop it to Data Flow design surface.
  2. Connect the CRM destination component to Derived Column component that we created in previous step.
  3. Double click the CRM Destination component to open its editing window.
  4. Choose the CRM connection manager that we previously created, select Update as the Action option. Then select "new_lesgislation" as the Destination Entity.
After this is done, our data flow development is finished. The entire development shouldn't take you more than a few minutes, if you have been acquainted with SSIS. So you will have a data flow like the following screen. 

Schedule SSIS Package

After we have developed the SSIS package, the next thing we need to do is schedule it using SQL Server. This should be typically handled by DB team.

SQL Server has great scheduling capability so that we can run tasks at scheduled time. I will not cover all the details about how to setup such scheduled jobs in SQL Server, which is a pretty straight-forward process using SQL Server Agents, and in most cases are handled by your DBA guys. The following screen shows the available scheduling options that you can use to run SSIS packages. 

After your DB team has setup the scheduled tasks for you, you can be rest assured that the business logics that you have implemented in SSIS packages will be fired based on the schedule that you have defined. 

Final Notes

  1. In the community, we often talk about scheduled CRM Workflow, which is not a feature available today. With the availability of SSIS Integration Toolkit, you can implement almost any such kind of business logics using SSIS. 
  2. As a disclaimer, SSIS Integration Toolkit is a commercial product, and I work for KingswaySoft, the provider of the tool. 
Thanks for reading, hope this helps. 

Blog Series - Better Together, SSIS and Microsoft Dynmaics CRM

For many of us who know Microsoft SQL Server Integration Services (SSIS), we tend to think SSIS as an ETL tool or a data integration platform.

But SSIS can do much more than just ETL and date integration, if it is used wisely.

In this series of blog posts, I will talk about how you can make use of SSIS in your CRM projects to help address some common challenges that you may often run into.
This series will probably take me a bit time to finish them all, I will try to post them as fast as I can. If you have any interests to contribute to the series or even you want to add more topics, please feel free to drop me a note by adding a comment below.
[UPDATE - July 26, 2012] I originally planned to write a series of 5 blog posts, but I have found that the last topic is a little boring. So I am concluding this blog series.

Thanks for reading, hope this series will help. 

Monday, May 07, 2012

Install Microsoft Dynamics CRM 2011 on Windows Server 8 (Windows Server 2012)

[UPDATE - March 28, 2013] Microsoft has released support for Windows Server 2012 in its UR13 update. With the availability of UR13, you should NOT need to read this blog post in the case that you intend to use UR13+ as your development environment. If your plan is to not use the latest rollup but rather stay with a rollup prior to UR13, then you can still use the techniques in this blog post. [/UPDATE]

If you like to live on the edge, you might want to use Microsoft latest operating system for your development environment, such as Windows 8. At the same time as a CRM developer, you would probably also want to use a Windows server system as your development environment, so you don't have to use a virtual machine, which consumes more computing resource, and takes longer to start up.

If you have the above thought, you are not alone. I have the exact same thought, which is the reason I generally use Windows server system for my CRM development environment.

During the weekend, I started to build up a new development environment using my new Lenovo ThinkPad T520 laptop (Regrettably I bought a laptop with no touch screen, which you should avoid if you in the market for a new laptop). So my natural thought would be using Windows Server 8 (Update - May 31, 2012, Windows Server 8 is now called Windows Server 2012).

[UPDATE - Sep 8, 2012] I was able to use the same approach to install CRM on the final release version (aka RTM) of Windows Server 2012.

However until I started the actual installation, I didn't realize that Windows Server 8 is not supported by Microsoft Dynamics CRM 2011. Here are the procedure and a few hacks that I used to get the job done. 
  1. Before you launch the CRM installation program, you should first install Windows Identity Foundation (WIF) using Server Manager
     
    It should be noted, you cannot install WIF using the download bits from Microsoft website, since they are not compatible with Windows Server 8. Also you cannot depend on CRM installation program to install WIF for you, it will always fail, since it basically tries to use the WIF installation package from Microsoft download site. You will always get "Installation failed" error, no matter how hard you click "Install" button in the following screen.
     
  2. Secondly, you need to make sure that you have IIS installed first. You can do so by adding Web Server (IIS) role using Server Manager. If you don't have IIS installed, you will encounter the following error during the installation. Having IIS installed will save you this trouble.
  3. Make a backup of svrmgrnc.dll file under your Windows System32 folder, since we are going to overwrite this file.
  4. In order for you to be able to overwrite svrmgrnc.dll file under System32 folder, you need to take over ownership of the file. To do so, you need to find the file under System32 folder, right-click on the file, and choose Properties, then navigate to Security tab, choose Advanced button, then click Change.
  5. Then you need grant yourself Write privilege, or Full Control privilege to svrmgrnc.dll file under System32 folder.
  6. After you have made the proper changes to svrmgrnc.dll file, the next thing you need to do is to find the following four files from a Windows 2008 R2 system, and copy them to your System32 folder of your Windows Server 8 system.
    • Microsoft.Windows.ServerManager.dll
    • rmConfigHelper.dll
    • ServerManagerCmd.exe
    • svrmgrnc.dll
  7. You can now start the CRM installation as usual.
  8. During the installation process, you will be prompted the following error window, just click OK to continue.
  9. As the installation goes further, you will run into another two errors, both are related to Windows Indexing Service which is no longer available for Windows Server 8. Those two errors will not affect the general CRM functionality, except CRM help search function, which I have never found myself in need of it anyway.
    Action Microsoft.Crm.Setup.Common.InstallHelpIndexesAction failed.
    Retrieving the COM class factory for component with CLSID {3BC4F3A1-652A-11D1-B4D4-00C04FC2DB8D} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).


    Action Microsoft.Crm.Setup.Server.StartIndexingServiceAction failed.

    The specified service does not exist as an installed service

  10. If you are as lucky as me, your CRM installation should finish with the following message.
  11. After the installation is done, you should recover the svrmgrnc.dll file that you made the backup in step 2, and also you are now free to remove the other three files from System32 folder.
After all this is done, I had a Windows Server 2012 system with SQL Server 2012, Visual Studio 2012, and Microsoft Dynamics CRM 2011 installed on the same box. I am pretty happy with it.

You might be wondering why we need to replace those four files. The reason is, Microsoft Dynamics CRM 2011 installation program uses a deprecated command line tool called ServerManagerCmd.exe to install and manage Windows features during the installation. ServerManagerCmd.exe depends on a few other libraries including rmConfigHelper.dll, svrmgrnc.dll, and Microsoft.Windows.ServerManager.dll.

Summary Notes

  • This is not a supported approach, so you may or may not want to go this path depending on your development needs.
  • CRM R8 is only inches away from the final release, you may want to wait for R8. There is a hope that CRM R8 might have Windows Server 8 support, which I am not sure at this moment yet. [UPDATE - May 10, 2012] CRM R8 is going to be RU9 (confusing enough? RU9=Rollup Update 9, R8=Release 8), which requires RU6. So in order to install R8, you need to first install RU6, so we don't have a way to install R8 on Windows Server 8 unless you use the hacks illustrated in this blog post. 
  • For your convenience, I have uploaded the mentioned 4 files in a zip file to my Skydrive, so feel free to grab it if that saves you time, or you just don't have a Windows Server 2008 R2 system available to get those files. Unless you really trust me, you may not want to use the file. ;-)
  • Use the hacks at your risk, I make no warranties of any kind.
Thanks for reading, hope this helps.

Friday, May 04, 2012

One and only CRM Ribbon editor that you will ever need

A while back, I blogged about a ribbon editor in the community. Recently another CRM community guru Scott Durow published his toolkit for CRM ribbon customization, which is called Ribbon Workbench.

Scott informed me about the tool soon after he made it available. I promised him that I would write a blog post about his tool, but I never got a chance to write about it. So here is a short introduction of the tool (Scott, I apologize for this overdue blog post).

A few benefits of the tool as far as my experience goes.
  • The tool was developed using Silverlight Web Resources, so it runs within CRM providing you an integrated experience with the application
  • The tool is more stable and more robust, I had never run into any crashes so far
  • The tool has probably the most complete features in terms of ribbon customization. Some important feature that are not commonly seen in other tool include the support of scaling, flyout menu, and many others (For a complete list of all the feature, please check the tool's download page). 
The following is a screenshot of the tool in CRM. 


Note that the tool is a freeware, it is available for download at Scott's company website

I noticed that Tanguy has published a blog post about the tool today, I am just trying to second his sentiment. 

Hope this help. 

Sunday, April 29, 2012

Apologize for the unfinished blog post

I accidentally published an unfinished draft blog post today, it was something in my queue that I have been trying to finish, but was not able to dedicate time to it. I published it by mistake after I clicked a wrong button.

I sincerely apologize for the confusions that it may have caused. I will try to make the post(s) available as soon as I can.

[UPDATE - May 10, 2012] The series of blog posts is available now, it is still currently working-in-progress.

Tuesday, April 24, 2012

The other half story about BDD, SSIS, and CRM

[NOTE - May 5, 2016] The config file changes are no longer necessary since our v7.1 release on May 5, 2016. The technique described in this blog post is still useful if you are using an older version or even if you may be using a different approach for data migration or integration.

This is an update of my previous blog post about using BDD component in SSIS to improve the performance of loading data into Microsoft Dynamics CRM.

I was only able to tell the half story of the technique in the previous blog post. Here is the other half.

In order to make full use of the BDD component in SSIS data flow, you need to increase the connection limit that is imposed by Microsoft .NET framework, which is a maximum of 2 connections per host (e.g. server). In order to overwrite this limit, you need to modify DTExec.exe.config and DtsDebugHost.exe.config files under DTS\binn folder by adding the following connectionManagement section.
<configuration>
 ...
 <system.net>
   <connectionManagement>
     <add address="*" maxconnection="100"/>
   </connectionManagement>
 </system.net>
</configuration>
The above configuration allows up to 100 connections per host at the same time. You may change the number based on your needs. Note that if you are using 64-bit system, you will need to make changes to the files under both Program Files and Program Files (x86) folders. 

After making the above changes, I observed more performance improvement in my SSIS data flow. I was able to load 0.9 millions of records into CRM within one hour (5 outputs and 10 outputs had almost identical performance benchmark, while 5 outputs outperformed 10 outputs a bit in my test). 

This has been reflected in our product FAQ page

If you write custom code to load data into CRM using multi-threading, you should make similar changes to your application's config file as well. 

It should also be noted, if your SSIS data flow reads or writes data simultaneously to a single host using web service interface, you should consider making this change, regardless it is WCF, WSDL service, or maybe even a REST service. 

This is a tip that I learned from CRM project manager Mahesh Hariharan at xRM Acceleration Lab during the week of Apr 2 to Apr 6, which was held at Microsoft campus in Redmond. 

BTW, in case you don't know, Microsoft has an xRM Acceleration Lab program which is open to Microsoft Dynamics CRM partners to help them build up the necessary technical skills to bring your solutions faster to the market. The lab is a one-week one at Microsoft campus, which consists of a number of technical sessions presented by CRM MVP folks and some product team members. The best part about the lab is, your team resources will be working with a gang of MVP folks (and some product team members) side-by-side to develop solutions during the week. This is a very efficient way to build up the technical skills by building something real on top of Microsoft Dynamics CRM platform. 

Hope this helps. 

Tuesday, April 10, 2012

Release: Service Pack 3 of SSIS Integration Toolkit for Microsoft Dynamics CRM

 Today, we are happy to announce the third service pack of SSIS Integration Toolkit for Microsoft Dynamics CRM, which is now available for download. The release includes the following updates and fixes.
  • Update: We made a minor change to License Manager program, which now prohibits possible duplicate license requests by clicking the OK button more than once in "Enter Product License" window
  • Fixed: In CRM Source Component Editor, when you enter a valid FetchXML, and immediately click "OK" button to save the change, the source component does not properly populate CRM fields due to the lazy-editing feature (for performance reason) used in the XML editor component
  • Fixed: When you update statecode and statuscode for salesorder or opportunity entity, you might run into exceptions
  • Fixed: The CRM destination component doesn't provide correct CRM record's ID in GUID value after the record has been created in CRM (Thanks to Javier for reporting this issue)
A new build is now available for download at our product download page.

In order to install the new build, you will need to uninstall the previous version first.

On a side note, from this post on, I will move such kind of product announcements to the blog section of my company website. But I might share some product news here if it is something technically interesting.

Thank you for reading.

Sunday, April 01, 2012

Why should you use CRM Developer Toolkit for your next CRM project engagment


[DISCLAIMER] This blog post contains strongly-opinionated information. For this reason, you are expected to use your judgement to decide whether the information is helpful for your development practice. As such, I make no warranties of any kind. [/DISCLAIMER]

Many of you are probably aware that I am a big fan of CRM Developer Toolkit. However, it is an unfortunate fact that the tool has not attracted enough attention in the community. Many of us are sceptical about using the toolkit with the concerns that the complexity or limitation of the tool might overweigh its benefits. This is reasonable for a new tool, and it is also expected.

With my limited experience with the toolkit, I have identified quite some important benefits when the tool is properly used. Here are some of them.

Unified Development Experience

The toolkit helps handle most day-to-day CRM development tasks. The toolkit supports the development of CRM web resources, Plugin, Silverlight, Workflow projects.

The toolkit provides a consistent approach for CRM development practice. Using the toolkit, you have one single Visual Studio solution to work with, which allows you to manage most (if not all) of CRM artifacts. The solution file include a CrmPackage project which is the container of CRM artifacts that you deploy and publish to CRM, including the source code of web resource files, references to plugin, workflow, Silverlight projects. If any of us have worked with SharePoint, you probably remember the days before its Visual Studio extension was available. If you asked 10 developers to do one single task for SharePoint, in most of cases, they would probably have done it in 9 different ways. It has been significantly better after the SharePoint extension became available. I believe CRM developer toolkit is an equivalent tool of the Visual Studio extension in SharePoint development practice, which has been widely embraced and welcomed by SharePoint community, and it has become part of Visual Studio shipment in VS 2010. I dare to say that nobody in SharePoint community wants to go back to the time without the extension. I would hope that the CRM developer toolkit can one day make into Visual Studio shipment as well, however I am very aware that we have a long way to go to get there.

Increased Development Productivity

The toolkit significantly improves your development productivity. Working with CRM web resources can be a very tedious task if you do it from CRM user interface. The process is significantly simplified, if you are using the toolkit. You make the changes to the web resource within Visual Studio, then you right-click CrmPackage project, and choose "Deploy" menu, the websource is deployed to CRM. It is as simple as a snap. The only thing you need to do after this is, you have to publish the changes in order to see the changes in effect. What I usually do is to always leave a web page open, which has a "Publish All Customizations" button on it, so that it takes me one single click to have the deployed changes published in CRM. Since the toolkit supports most CRM development tasks, you rarely need to leave the environment. Also the toolkit has included a few other productivity tools including the capability to create a plugin class, and generate early bound entity classes from CRM Explorer, etc.

Managed Development Process

The toolkit manages your CRM web resources so that you can source control them. The toolkit allows you to manage your CRM web resources using file system within Visual Studio, each web resource is represented by one physical file in CrmPackage project. Such files can be easily version controlled using your favorite source control system. In the good CRM4 days, I have used some code snippet to load JavaScript from file system, so that I can version control my JavaScript files. This is totally unnecessary with the help of the develop toolkit in CRM2011 (also thanks to the Web Resource feature).

Advocate of Best Practices

The toolkit provides some guidelines for CRM development practice, and also it advocates some best practices when working with CRM. It has got a plugin project template in the Visual Studio solution for us to start with, which includes quite some informative comments.

Free of Charge

Last, but not the least, the toolkit is free, it comes with CRM SDK at no charge. ;-)


With all the above benefits using the toolkit, you should also be mindful about the constraints or limitations when using the tool. It is particularly important to know the boundary of the tool, and know what the tool can do, what it can't do or should  not be used. With the help of the toolkit, we, the developers, are really the director of the development. The tool has its own way to get the job done, it is up to us to leverage its flexibility to make our development life more enjoyable. After all, CRM is the final judge, the toolkit won't cover 100% of development scenarios, but there is usually some workaround when it comes to CRM platform, the primary value of the tool is to help streamline the process of working with CRM, so that we can be more productive.  Some in the community may say that the toolkit is just a CRM metadata browsing tool. This could be the case for the toolkit's CRM4 version, and it is definitely not the case for CRM 2011 developer toolkit, it is much more than a CRM metadata tool.

Although the toolkit is overall a great tool, it doesn't mean it is perfect. One important feature that is currently missing in the toolkit is the ability to deploy individual WebResource (CRM MVP Rhett Clinton and me both raised this as a feature request). I think this is an important feature to make the toolkit work better in a shared team development environment. For instance, if we have two developers using the same DEV environment, both of them are working on a different piece of web resource at a time. With the current version of toolkit, it will deploy the entire project when the Deploy command is used, so what would typically happen is, one developer will overwrite another developer's latest change. If we have the capability to deploy individual file, it will not cause this kind of trouble. Please vote for the change if you agree with me, so let the voice heard. Also there are also other areas that could make the toolkit better, if you have any other feature requests, I would encourage you to use Microsoft connect site to report them to Microsoft.

Thanks for reading.


Wednesday, March 28, 2012

Release: XrmSvcToolkit v0.1 (a JavaScript Library for Microsoft Dynamics CRM 2011)

I am happy to announce the availability of XrmSvcToolkit v0.1, an upgraded version of CRM Web Service Toolkit, which now works with Microsoft Dynamics CRM 2011 by using the latest SOAP and REST endpoints.

The following functionalities are currently supported by the toolkit for SOAP and REST endpoints.
  • createRecord (REST)
  • updateRecord (REST)
  • deleteRecord  (REST)
  • retrieve  (REST)
  • retrieveMultiple  (REST)
  • associate  (REST)
  • disassociate  (REST)
  • setState (SOAP)
  • fetch (SOAP)
  • execute (SOAP)
There is a dependency of the toolkit, which is the JSON library that has been included in the downloadable solutions on codeplex website. The signature of all functions have been changed from previous version, so it may take a little effort to get used to. But if you have ever worked with jQuery, the interface may sound familiar to you. Hope you like the change.

To get started with the toolkit, you can import the solution files from codeplex website, or upload the provided JS files as web resources to CRM. Then you can add the library to your form so that you can consume it in your form script. Include both json2 and xrmservictoolkit in your form library, and make sure to have json2 before the toolkit. 

Where is the documentation?

As a matter of fact, I haven't invested much time writing the documentation of the toolkit. My fellow CRM MVP Mitch Milam is working on writing documentation for the toolkit in his secret upcoming project. I am counting on him.

In the meantime, you can check sample code in the accompanied unit test file (XrmSvcToolkitTest.aspx), which illustrate the typical scenarios how the toolkit can be used. 

Some Final Notes

I would like to apologize for the wait of the community for a new version of the toolkit, the primary reason is that I was late to the CRM 2011 party, and the new service interface was quite a challenge for me to have a level of confidence to get into the implementation of an upgrade.

One thing that I should point out is, after I have finished all the coding of the toolkit the other day (Feb 22, I believe), I was preparing for the release, so I started to create a new project on codeplex. I first picked a name of xrmservicetoolkit, but immediately realized that the project name had already been taken. What I noticed was that another community member (jaimieji) had already published an open source project using this name, which was doing something very similar. I had a look of the source code, it was a pretty good quality implementation. You may want to have a look of his implementation, as he has provided more functionalities than mine.

Also, Daniel René Thul has his implementation as well. His implementation is interesting and is based on jQuery, which you may want to check out as well.
I have been holding the release of the toolkit for about a month, until Codeplex sent me a final threatening email today that my project will be deleted from their system if I don't publish the project today.

Also, it should be noted, my implementation is not feature-rich enough to satisfy all your development needs. But I hope it can be used as a starting point or reference for your project engagement, and it should not be hard to add new features to the toolkit.

Since CRM R8 is going to be a cross-browser version according to Microsoft public announcement recently, there is some work to be done in order to support different browsers. Hopefully I am not occupied at the time so that I can provide an upgrade swiftly. 

Friday, March 23, 2012

Release: Service Pack 2 of SSIS Integration Toolkit for Microsoft Dynamics CRM


Today, we are happy to announce that the second service pack of SSIS Integration Toolkit is now available for download. The release includes the following updates and fixes.
  • Update: We added a few social buttons to the Connection Manager Editor interface, including an email button, to make it easy for you to reach us
  • Update: We added an icon to the head cell of CRM Destination Component's grid, when Upsert action is selected, and Manually Specify option is chosen as Upsert Matching Criteria. This provides an indication of the column's importance (Thanks Scott for the advice)
  • Update: We disabled the ability to change CRM Connection Manager's service endpoint after it has been created. The reason is, different service endpoint has incompatible metadata. Making change to service endpoint often causes problems to the CRM Source Component or CRM Destination Component that uses the connection manager
  • Update: There is a small change to the License Manager program, so that it now prompts you to save the requested (or activated) license to a file if the attempt of saving the license to Windows registry fails because of insufficient privileges on the system. This gives you the possibility to later install the license file using a different user account in the system
  • Fixed: You might experience the following error message in some environment when you are using SOAP 2011 service endpoint for CRM Source component. "System.ServiceModel.FaultException`1[KingswaySoft.DynamicsCrmServices.Soap2011.CrmOrganizationService.OrganizationServiceFault]: Paging cookie is required when trying to retrieve a set of records on any high pages. (Fault Detail is equal to KingswaySoft.DynamicsCrmServices.Soap2011.CrmOrganizationService.OrganizationServiceFault)." (Thanks to Myles for reporting this issue)
  • Fixed: You might experience the following error message when you are using "Manually Specify" option for Upsert action, with a complex type field (i.e. lookup) selected as the matching field. "There was an error while trying to serialize parameter http://schemas.microsoft.com/xrm/2011/Contracts/Services:query. The InnerException message was 'Type 'KingswaySoft.DynamicsCrmServices.Soap2011.CrmOrganizationService.EntityReference' with data contract name 'EntityReference:http://schemas.microsoft.com/xrm/2011/Contracts' is not expected. Add any types not known statically to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer.'.  Please see InnerException for more details." (Thanks to Jean for reporting this issue)
A new build is now available for download at our product download page.

In order to install the new build, you will need to uninstall the previous version first.

Thank you for reading. 

Wednesday, March 21, 2012

Quick Summary: Microsoft Convergence 2012, Day 4

It's the final day of the event.
Richard Knudson has a nice blog post which summarized the the following four important things from the event. 
  • Big data
  • Cloud
  • Social
  • CRM Anywhere
Richard mentioned that he will host a webinar on Wednesday April 4 to share some more details about the event.

Before we conclude the post, here are three more notes from myself. 
  • Metro and Windows 8 will have some big impacts on the future changes of Microsoft Dynamics CRM platform from user experience perspective. 
  • We might have a robust database engine that is big data ready, but CRM is not really there yet, which is a challenge ahead of us, in my humble opinion. 
  • Another thing that you should watch out is, we all know that we are going to have the cross-browser support in R8. What this brings to us is, we need to write JavaScript code that works for different browsers.  This is not an issue if we only use SDK API, however there are always cases that we need to do a little magic which is not achievable using the SDK API. This is where your talent comes into play.  
This concludes my notes of the event. It was definitely a fascinating event even I was not at the scene. I look forward to being there myself next year. 

Tuesday, March 20, 2012

Quick Summary: Microsoft Convergence 2012, Day 3

It's the third day of Convergence 2012, the event went to its way for more technical details. It might be a pouring weather there, so are the sessions.
The event wraps up tomorrow with a number of technical sessions on their way. 

Monday, March 19, 2012

Quick Summary: Microsoft Convergence 2012, Day 2

Although I am not at the scene, I can smell all the heats, passions at Microsoft Convergence 2012 in Houston, Texas.

In case, you are in the same situation as me that you weren't able to make to the event, here's some fresh juice for you to taste what the event looks like on its second day, which I think is the actual beginning of the show.
  1. Microsoft Dynamics CRM has been mentioned more times than any other Dynamics products in the key note delivered by Kirill Tatarinov (President, Microsoft Business Solutions Division) and Kevin Turner (Chief Operating Officer, Microsoft). 
  2. A lot of details of the upcoming Dynamics CRM R8 release have been revealed in the Microsoft Dynamics CRM key note session delivered by Dennis Michalis (General Manager, Microsoft Dynamics CRM), Craig Dewar (Director - Product Management, Microsoft Dynamics CRM), including cross-browser support, mobile client, social enhancements, SQL Server 2012 support, snapshot of data, Office 365 integration, etc.
  3. There was a demo of upcoming Dynamics CRM metro-style interface, it is very impressive. I suspect that this is not coming with R8, it is most likely something for R9, which is scheduled for the second half of this year (Novemberish). 
  4. A LinkedIn connector/solution for Microsoft Dynamics CRM was announced today, and is coming next week
  5. Microsoft Dynamics CRM Online and Office 365 provides better value together by offering streamlined business process, and integrated user experience, etc.
  6. Snapshot of data could be something very beneficial to CRM online customer. 
  7. There were two analytics sessions for Microsoft Dynamics CRM today which demonstrated some advanced scenarios of business intelligence development for Microsoft Dynamics CRM. 
The list is based on the twits that I read from twitter, which was composed based on my interpretation and speculation, I make no warranties of any kind for the accuracies of the information. Also it is very possible that I could have missed something important in the list, please do let me know if so.

Tomorrow, there are some great sessions coming, including a few sessions that focus on integration, which you shouldn't miss if you are a technical person. If you are more business-focused, you will find a lot of sessions that specifically talk about how you leverage Microsoft Dynamics CRM platform to realize your business values through sales, marketing and service automation.

Monday, March 05, 2012

Release: First Service Pack of SSIS Integration Toolkit

Following our release of SSIS Integration Toolkit for Microsoft Dynamics CRM, we have received a warm welcome by the community. Thanks everyone.

Today, we have released our first service pack for the toolkit which includes the following bug fixes.
  • Fixed: CRM destination component throws error when writing data to currency fields, if SOAP 2011 endpoint is used (Thanks Les for reporting this issue)
  • Fixed: When CRM Source Component Editor is re-opened, the SSIS metadata of CRM fields are always repopulated. If you click OK to dismiss the window without making any changes to the source component, it invalidates the mappings for all the downstream components
  • Fixed: CRM destination component does not handle writing activityparty fields properly for SOAP 2011 endpoint
  • Fixed: You might experience expired token error if your data flow task runs for hours, when SOAP 2011 endpoint is used for CRM Online or IFD deployment
A new build is now available for download at our product download page.

In order to install the new build, you will need to uninstall the previous version first.

Thanks for reading. 

Sunday, February 19, 2012

CRM2011: Get the Right Server URL (Silverlight Version)

After my previous post about how to get the right server URL in CRM JavaScript code, I was asked to provide a translation of the code to C# so that it can be used in Silverlight web resource. So here is post along with the translated code.
public static string GetServerUrl()
{
    var context = GetContext();
    var isOutlookClient = (bool)context.Invoke("isOutlookClient");
    var isOutlookOnline = (bool)context.Invoke("isOutlookOnline");

    var documentUri = HtmlPage.Document.DocumentUri;

    var localServerUrl = string.Format("{0}://{1}:{2}", documentUri.Scheme, documentUri.Host, documentUri.Port);
    if (isOutlookClient && !isOutlookOnline)
    {
        return localServerUrl;
    }

    var baseServerUrl = (string)context.Invoke("getServerUrl");
    var serverUrl = Regex.Replace(baseServerUrl, @"^(http|https):\/\/([_a-zA-Z0-9\-\.]+)(:([0-9]{1,5}))?", localServerUrl);
    return Regex.Replace(serverUrl, @"\/$", string.Empty);
}

// This is the same code that comes from CRM SDK in its SoapForSilverlightSample project.
private static ScriptObject GetContext()
{
    var xrmProperty = (ScriptObject)HtmlPage.Window.GetProperty("Xrm");
    if (null == xrmProperty)
    {
        //It may be that the global context should be used
        try
        {
            var globalContext = (ScriptObject)HtmlPage.Window.Invoke("GetGlobalContext");

            return globalContext;
        }
        catch (InvalidOperationException)
        {
            throw new InvalidOperationException("Property \"Xrm\" is null and the Global Context is not available.");
        }
    }

    var pageProperty = (ScriptObject)xrmProperty.GetProperty("Page");
    if (null == pageProperty)
    {
        throw new InvalidOperationException("Property \"Xrm.Page\" is null");
    }

    var contextProperty = (ScriptObject)pageProperty.GetProperty("context");
    if (null == contextProperty)
    {
        throw new InvalidOperationException("Property \"Xrm.Page.context\" is null");
    }

    return contextProperty;
}
If you are talking to CRM SOAP endpoint, you may get the complete URL using the following code.
Uri serviceUrl = new Uri(GetServerUrl() + "/XRMServices/2011/Organization.svc/web");
If you need to talk to CRM REST endpoint, you may get the complete URL using this one.
Uri serviceUrl = new Uri(GetServerUrl() + "/XRMServices/2011/OrganizationData.svc");
Hope this helps.

Friday, February 17, 2012

Improve CRM Data Load Performance by using BDD in SSIS

[DISCLAIMER] This blog post mainly talks about the benefits of our commercial offering, but the practice should be generally applicable to other approaches or components that you might be currently using in SSIS. It is not my intention to turn my blog into a commercial space, but I do believe this blog post would help even your are using something different, assuming that SSIS is the tool for your data integration purpose. [/DISCLAIMER]


If you have ever been engaged in any Microsoft Dynamics CRM data integration project, I am relatively sure that you have invested time and resources to tune your data integration component to its maximum possible performance so that it takes the least time to finish the data integration tasks.

This blog post shows you how to load one million records into Microsoft Dynamics CRM 2011 on-premise installation with a two-hour time range, using our product - SSIS Integration Toolkit for Microsoft Dynamics CRM, by taking advantage of the Balanced Data Distributor (BDD) component that Microsoft released to public community that works for SQL Server Integration Services (SSIS).

In case you don't know BDD component, here is a little background information about the component. BDD is a data flow transformation component that takes a single input and evenly distributes the incoming rows to one or more outputs uniformly via multithreading. The purpose of BDD component is to maximize the output performance of ETL data flow tasks. BDD can be used when your downstream pipeline component (say the destination component) is the bottleneck of the entire data flow task.

When working with Microsoft Dynamics CRM data integration, we have a perfect reason to use BDD, mainly because writing data into CRM is slow due to the nature of web service interface. In other words, In most of cases, you would find that the CRM destination component which writes data into CRM is the bottleneck of your data flow tasks. Using BDD, we can distribute incoming rows from upstream pipeline components and split them into multiple CRM destination components, so they write data into CRM simultaneously and concurrently by taking advantage of the multi-threading capability of SSIS engine.

To demonstrate the benefits of using BDD component, I first tried a single CRM destination component in my data flow task without using BDD, so the data flow writes data into CRM using a single thread. It took me 5 hours, 48 minutes to finish the load of 1,000,000 record into CRM contact entity. Here is what the data flow task looks like.
The following screen shot shows how the data flow runs using dtexec command line.

Next, I tried to use BDD and split the input into 10 outputs so that we write to CRM contact entity using 10 concurrent threads. The data flow finishing loading 1,000,000 records within 2 hours, 3 minutes. Here is what the data flow task looks like.

The following screen shots shows how the data flow runs using dtexec command line.
The improvement is about 2.84 times, it's not surprising that it's not exactly 10 times faster. 

[UPDATE - Apr 24, 2012] In order to make full use of the BDD component, you need to increase the connection limit that is imposed by Microsoft .NET framework, which is a maximum of 2 connections per host (e.g. server) for service calls as far as CRM platform is concerned. In order to overwrite this limit, you need to modify DTExec.exe.config and DtsDebugHost.exe.config files under DTS\binn folder by adding the following connectionManagement section.
<configuration>
 ...
 <system.net>
   <connectionManagement>
     <add address="*" maxconnection="100"/>
   </connectionManagement>
 </system.net>
</configuration>
The above configuration allows up to 100 connections per host at the same time. You may change the number based on your needs.

After making the above changes, I observed more performance improvement. I was able to load 0.9 millions of records within one hour (5 outputs and 10 outputs had almost identical performance benchmark). Note that this was done on a desktop computer, if you have a better server with performant IO and more computer power, I am relatively sure that you can load one million records within one hour. 

A few facts

  1. This is not a scientific benchmark. 
  2. My testing was conducted on a desktop computer of 4-year old which has everything installed in the single box. The following is the spec of the computer. 
    • Processor: Intel Core 2 Quad Q9550 @2.83GHz
    • Memory: 8GB PC2-6400 DDR2-SDRAM
    • Hard Disk: Seagate 7200RPM SATA 1.5Gb/s
    • Operating System: Windows 2008 R2 Server
    • Database Server: SQL Server 2008 R2
    • Microsoft Dynamics CRM Server 2011 with Rollup 6
    • SSIS Adapter: KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics CRM
  3. The testing was done in an on-premise environment, your data load performance would be different if you are using CRM online or partner-hosted environment. 
  4. I have intentionally used 64-bit dtexec.exe with the hope that we can take advantage of SSIS 64-bit run-time. Controversy to what I believed, running it using 32-bit dtexec.exe is actually not slower, but 10% faster than 64-bit runtime. The reason is probably related to the cost associated with memory addressing in 64-bit runtime. 
  5. My input data is very simple, it has only two fields, firstname and lastname. When you have more fields, you would expect the data load performance to degrade in certain scale. 
  6. The single-destinationed data flow task writes about 47.84 records to CRM server per second (54.27 records/s when 32-bit runtime is used), you may use this as a baseline rate if you want to compare yours with mine. 

Summary

  • BDD improves the data load performance by taking advantage of the multi-threading capability of SSIS engine. 
  • You should carefully choose a right number of the outputs for BDD component. It's not the case that the more the better. Depending on your servers' capacity (including processor, memory, IO system) and the network latency between your client system and CRM server, it could be 3, 5, 10, or something else for the maximized performance, which you may find out by running different tests. 
  • There are many ways that you can use to improve the data load performance, BDD is just one of the easy ways that make the data load faster, which is the main topic that we are trying to cover in this blog post.  
If you are interested in any of the data flow tasks or sample data, please feel free to let me know, so that I can send you the SSIS package.

Thanks for reading.