Presentation Deck and Demo Code for XrmVirtual Presentation

Thanks to all who have participated in the XrmVirtual session yesterday.

The following is the presentation deck that I used yesterday.



The demo code is also available for download at the following URL.

There are three solutions in the zip file, which are:

• CrmWebServiceIntegration, this solution contains two projects which show you how to load data from an .csv file to CRM using early bound and late bound programming style. 
• AzureServiceBus, this solution demonstrates the capability that you can use CRM Azure Service Bus in CRM plugin. In order for the solution to work, you need to first configure a service endpoint using CRM plugin registration tool, that you can use in the plugin code. 
• OnPremiseServiceBus, this solution hows to send message to Service Bus for Windows Service (on-premise installation) from CRM plugin. 

In order to run the sample solutions, you need to update the configurations in the code. Those changes can be found from the project if you search "TODO" keyword. I have used hard-coded configuration for simplicity reason. 

I used CRM developer toolkit for the two plugin projects. In case you have trouble opening the AzureServiceBus project, it is probably because you don't have CRM developer toolkit installed. And also, I was using Visual Studio 2010 instead of VS2012. If you are using VS2012, you might have trouble to deploy the plugin from Visual Studio environment.

Please note that the sample code is for demonstration purpose, and it is NOT production quality code. 

The presentation's video recording is also available for download on XrmVirtual site if you are interested in the topics. Watching the video, you would have to bear with my accent, and my poor presentation skills.

Presentation on Enterprise Data Integration for Microsoft Dynamics CRM

I will be presenting for XrmVirtual tomorrow by the topic of Enterprise Data Integration for Microsoft Dynamics CRM. The session starts at 9am PST Apr 9th, 2013, and it will first kicks off by CRM SDK team to share with us some more recent updates in the latest SDK release (v5.0.15) related to UR13 and CRM Online updates.

My session will starts right after CRM SDK team's announcements and updates. In the session, I will try to cover some data integration options that you might have when working with Microsoft Dynamics CRM projects, including CRM SDK programming, Import Data Wizard, ETL, Service Bus, etc. The session will be concluded by sharing some tips and tricks that I learned when working with some large-scale data migration and integration projects, and also some lessons that I learned while designing and developing SSIS Integration Toolkit for Microsoft Dynamics CRM.

The session has been planned to be a one-hour session which includes CRM SDK team's updates, but I have got the permission from XrmVirtual team that the session can go a little longer (mainly due to the amount of information to be covered in one session), so the entire session could run for about 90 minutes although the original invite is only for 60 minutes.

The following is the download link to the meeting invite, you are more than welcome to join the session if this interests you.


Note that the session will be recorded by XrmVirtual team. If you can't make it, you will still have a chance to view the presentation at a later time. Further information about the session can be found at XrmVirtual website.

XrmVirtual is a user group for professional CRM developers who are passionate about Microsoft Dynamics CRM, and is run by two Dynamics CRM MVPs, Julie Yack and Shan McArthur.

See you then!


[UPDATE - Apr 10, 2013] I have uploaded the presentation deck file and demo code that you can download, check out here. The presentation's video recording is also available for download on XrmVirtual site if you are interested. Watching the video, you would have to bear with my accent, and my poor presentation skills.

The other half story about BDD, SSIS, and CRM

[UPDATE - July 7, 2017] In our most recent v9 release, we have added the support of multi-threaded writing in our CRM destination component, so this blog post is no longer useful. You can simply turn on multi-threaded writing in the destination component to achieve the same (with much greater flexibility or even better performance). [/UPDATE]

[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. 

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.

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. 

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. 

Improve CRM Data Load Performance by using BDD in SSIS

[UPDATE - July 7, 2017] In our most recent v9 release, we have added the support of multi-threaded writing in our CRM destination component, so this blog post is no longer useful. You can simply turn on multi-threaded writing in the destination component to achieve the same (with much greater flexibility or even better performance). Also, the number discussed in the blog post is super outdated. I am keeping the blog post here really just for reference purpose, you should not use the numbers as your benchmark baseline. [/UPDATE]

[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 screenshot 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 number that I have got is on a desktop computer with everything installed on the same box, it should NOT be considered as a performance benchmark. Also the techniques discussed in the blog post are outdated at today's standard. I am keeping the blog post really just for reference purpose. 

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.

New Licensing Model and Pricing Structure of SSIS Integration Toolkit

Following my previous announcement of the availability of SSIS Integration Toolkit for Microsoft Dynamics CRM, we received quite some feedback about the offering. One of the feedbacks that we received was the licensing model was too complicated to understand and the price was a little too high.

For this reason, we have adjusted the licensing model and also updated pricing structure, so it is a lot simpler and cheaper than the previous one.

Under the new licensing model, we only have two product licenses, and one maintenance & support term.

SSIS Integration Toolkit for Microsoft Dynamics CRM, Perpetual License	$895.00
SSIS Integration Toolkit for Microsoft Dynamics CRM, One-year Subscription License	$495.00
SSIS Integration Toolkit for Microsoft Dynamics CRM, One-year Maintenance & Support	$395.00

A new build of the software has been uploaded to accommodate those changes, which also includes some important bug fixes since the announcement.

No license is required any more (not even a trial license) if you just need to create an SSIS package using the toolkit, or play with the toolkit within SQL Server Business Intelligence Development Studio. However, you should acquire a license if you want to deploy the SSIS packages to an environment where you want the packages to be executed on a scheduled basis.

Thanks for reading.

Introducing SSIS Integration Toolkit for Microsoft Dynamics CRM

Today, I am happy to announce the availability of SSIS Integration Toolkit for Microsoft Dynamics CRM, an easy-to-use and cost-effective data integration library for Microsoft Dynamics CRM and Microsoft SQL Server Integration Services (SSIS).

First of all, this is a commercial offering, which we hope to bring a better solution to the market that can make your data integration job easier.

The toolkit is a set of SSIS adapters that allow you to read data from Microsoft Dynamics CRM server, or write data to it.

Among all the goodnesses, here are some highlighted features that you might be interested.

• Native CRM 2011 SOAP interface support
• Support for Microsoft Dynamics CRM 2011 and 4.0
• Support for On-premise, IFD (federated) and Online deployments, including Office 365 (OSDP) environment
• Support for CRM Entity or FetchXML query as data source in CRM source component
• Support for Create/Update/Delete/Upsert actions in CRM destination component. When Upsert action is used, you have three matching options to choose to identify whether it is an existing record in CRM for update, or it's something not in the system that the component should create for you (insert). The three options include a combination of manually selected fields, CRM primary key, or CRM duplicate detection
• One-click to toggle whether you want all or none of fields from a CRM entity in source component, which makes you more productive when selecting the only necessary fields to read data from CRM
• Automatic mapping to the CRM fields in the destination component by matching the names from upstream components
• You can set a picklist/optionset field value by either its integer value or its user friendly label
• Native support of CRM many-to-many relationship to associate or disassociate CRM records in CRM destination component by using Create or Delete action

Here are some more details about the component along with some screen shots.

Overview
SSIS Integration Toolkit for Microsoft Dynamics CRM provides a cost-effective solution that helps integrate Microsoft Dynamics CRM with other data sources or applications.

SSIS Integration Toolkit takes advantage of the flexibility and power of Microsoft SQL Server Integration Services (SSIS) platform, which made it possible to integrate with virtually any application or data source that you may need to work with.

SSIS Integration Toolkit is extremely easy to use. It comes with the following three major components to help facilitate the integration development.

• Microsoft Dynamics CRM Connection Manager
• Microsoft Dynamics CRM Source Component
• Microsoft Dynamics CRM Destination Component

The ultimate goal of the toolkit is to simplify the integration development work, so that you can spend more time focusing on resolving business issues. A data migration / integration development can be done as quickly as a few minutes, instead of weeks or months.

Microsoft Dynamics CRM Connection Manager

Microsoft Dynamics CRM Connection Manager allows you to setup a connection in SSIS so that it can used by Microsoft Dynamics CRM Source Component to retrieve data from Microsoft Dynamics CRM Server, or Microsoft Dynamics CRM Destination Component to write data to Microsoft Dynamics CRM server.

The connection manager offers the capability to connect to a Microsoft Dynamics CRM server which can be either version 2011 (including CRM Online) or 4.0. It supports both CRM 2011 SOAP endpoint and CRM SOAP 2007 Web Service interface.


CRM Connection Manager allows you to choose one of the following authentications based on how your CRM server is deployed or managed.

• Active Directory (On-Premise)
• Federated (On-Premise, Partner-hosted or Online Federation)
• LiveId (CRM Online)

Microsoft Dynamics CRM Source Component
Microsoft Dynamics CRM source component is a source component that you can use in SSIS data flow tasks, which allows you to read data from Microsoft Dynamics CRM Server.

Using Microsoft Dynamics CRM source component, you can pick a CRM entity to read data from, after a CRM connection has been chosen.


Microsoft Dynamics CRM source component also supports reading data from CRM server through using FetchXml queries.


When FetchXML option is used as the Source Type, CRM source component can intelligently identify all fields and corresponding metadata including the fields from linked entities.


When Entity option is used as the Source Type, Microsoft Dynamics CRM Source Component allows you to select the CRM fields that you want to obtain data, which makes it easy when you have a large number of fields in CRM system, but you only need a small number of them to be used in the downstream pipeline components.


Microsoft Dynamics CRM Destination Component

Microsoft Dynamics CRM destination component is a destination component that you can use in SSIS data flow tasks, so that you can write data to Microsoft Dynamics CRM Server.
Using Microsoft Dynamics CRM destination component, you can choose a CRM entity to write data to, and the action of such operation, which could be Create, Update, Delete or Upsert.


CRM Destination Component can be smart enough to automatically map all available input columns from upstream components to corresponding CRM fields based on their names, and it also allows to change such mapping using a dropdown list.


CRM destination component supports CRM many-to-many relationship entities, in which case, you can use Create action to create associations, use Delete action to disassociate them.

CRM destination component supports upsert action, in which case, the component will first try to check if the provided CRM record exists in CRM system, the component will only create a new record if the matching record doesn't exist. If the component has identified any existing records based on the matching criteria provided, it will update the matching records.

CRM destination component's upsert action can use the following three matching criteria.

1. Primary Key
2. CRM Duplicate Detection
3. Manually Specified Fields

For Create and Update action, CRM destination component allows you to choose "Enable CRM Duplication Detection" option.

When writing data to Microsoft Dynamics CRM, dealing with picklist / optionset imposes quite some challenges since CRM can only take integer numbers for them. Microsoft Dynamics CRM destination component has made this significantly easy, for any picklist / optionset fields (including statecode and statuscode fields), you can use either internal integer values, or their string values (user localized labels).

That's All
For more details about the offering, please proceed to my company's website at http://www.kingswaysoft.com.

Please give it a try, and let us know how it goes.

A free trial license can be requested after the installation using the accompanied License Manager program.

Hope you like this offering.