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.

MSCRM 4.0: A Few Tips on Using CRM Configuration Data Utility

In case you are not aware, CRM Configuration Data Utility is a handy tool released by Microsoft sometime last year. What it does is to help you transfer CRM configuration data from one environment to another.

I recently happen to have the need to transfer some configuration data between two environments, so I spent a little time to poke around with the utility, and I am very pleased with the tool, but I quickly realized a few limitations with the toolkit that have been intentionally engineered by Microsoft team. Fortunately the utility comes with complete source code, so I was able to tweak it to meet my needs. Here are a couple of tips that might help you make best use of the tool.

1. This utility is a really convenient tool if you want to transfer any configuration data stored in your custom entities. You can export data from one environment, and import to another one within a few mouse click. But here is the catch, it's engineered to only support the export and import of custom entities. But with the source code, you can easily change it to support any entities including system ones. What you need to do is to change the following code in ExportDataForm.cs file (RetrieveEntitiesComplete method)
   

   IEnumerable<EntityMetadata> entities = response.CrmMetadata
       .Cast<EntityMetadata>()
       .Where(entity => entity.IsIntersect.Value == false && entity.IsImportable.Value && entity.IsCustomEntity.Value)
       .OrderBy(entity => entity.DisplayName.UserLocLabel.Label)
       .ToList();
   
   

   to
   

   IEnumerable<EntityMetadata> entities = response.CrmMetadata
       .Cast<EntityMetadata>()
       .Where(entity => entity.IsIntersect.Value == false && entity.IsImportable.Value)
       .OrderBy(entity => entity.DisplayName.UserLocLabel.Label)
       .ToList();
   
   

   
   
2. The utility is a smart tool, but it is a little bit too smart when it comes to the import. It will actually try to do duplicate check before importing the data. The duplicate check is based on the imported entity's primary attribute, which isn't correct all the time. Sometimes, we do have two different CRM records with the same value of the primary attribute. For instance, we have a self-referenced entity, which has a parent field to refer to the same entity itself. It's often the case that the primary attribute of two CRM records are the same, but they are linked to two different parents, they are essentially two different records in the business world. In order to have the utility to do straight import, you should comment out the following code in dynamicentityutility.cs file (GetByIdOrPrimaryAttribute method)
   

   // Locate the following code and comment out the entire block in dynamicentityutility.cs file
   // in order to stop the configuration data utility from performing duplicate check. 
   string primaryAttribute = MetadataUtility.RetrievePrimaryAttribute(metadataService, entityName);
   QueryExpression query = new QueryExpression(entityName)
   {
       ColumnSet = new ColumnSet(fields)
   };
   
   ConditionExpression primaryAttributeExpression = new ConditionExpression(primaryAttribute.ToLower(), ConditionOperator.Equal, primaryAttributeValue);
   query.Criteria.Conditions.Add(primaryAttributeExpression);
   
   RetrieveMultipleRequest request = new RetrieveMultipleRequest()
   {
       Query = query,
       ReturnDynamicEntities = true
   };
   
   RetrieveMultipleResponse response = (RetrieveMultipleResponse)_crmService.Execute(request);
   
   if (response.BusinessEntityCollection.BusinessEntities.Count > 0)
       resultList.AddRange(response.BusinessEntityCollection.BusinessEntities.ConvertAll<DynamicEntity>(x=> (DynamicEntity)x));
   

The beauty about this tool is, you often configure your CRM workflows in one environment (for instance, your development environment), and those CRM workflows sometimes refer to particular CRM records as configuration data, so there is dependency between your workflow and those CRM records. When you export those workflows and import to a different environment (for instance, your production environment), you will have to re-create the same set of CRM records in the new environment in order to make the workflows work. But if you create the records manually, the records' internal ID (Guid) would be different, so you will have to reconfigure each workflow to point to the new created CRM records, otherwise, you would not be able to publish your workflows. But with the configuration data utility handy, you can simply transfer the configuration data, the IDs will remains the same as what it was in the original environment, so you don't have to reconfigure your workflows, it will save you tremendous effort when you try to promote workflows from one environment to another which have dependency on configuration data.

Hope this gives you some basic ideas about how to tweak the utility. In my opinion, it's a pretty well-engineered piece of software.