Packed and Ready to Go, See You at Convergence 2013 - New Orleans

I want to say that I am very excited that I am on my way to New Orleans to attend Microsoft Dynamics Convergence 2013. This is my first time attending Convergence, and I am really looking forward to this great event.

If you are also coming to Convergence, it might be a great opportunity to meet up for a chat or a coffee. The following is the list of my engagements in the event if you want to catch up with me.

Date	Time	Assignment / Session	Location
Mondy, March 18, 2013	4:00 - 6:00pm	Attending Microsoft Booth	Dynamics CRM Kiosk #4
Tuesday, March 19, 2013	12:00 - 3:00pm	Attending Microsoft Booth	Dynamics CRM Kiosk #4
	5:00 - 6:00pm	Ask the MVPs: Discussion with Microsoft Dynamics CRM MVPs (Facilitated by Donna Edwards, joined by a total of 10 MVPs as panel members including me)	Room #386
Wednesday, March 20, 2013	10:30am - 1:00pm	Attending Microsoft Booth	Dynamics CRM Kiosk #4
	2:00pm - 3:00pm	Ask the MVPs: Discussion with Microsoft Dynamics CRM MVPs (Facilitated by Donna Edwards, joined by a total of 10 MVPs as panel members including me)	Room #386

I just started my 10-hour connected flights to New Orleans, I look forward to seeing you there.

Updated: Microsoft Dynamics CRM 2011 JavaScript Development Cheat Sheet

I just made some updates to CRM 2011 JavaScript Development Cheat Sheet. There are a couple changes in this update to reflect some recent SDK updates.

• added Xrm.Utility Methods
• added getClientUrl method
• marked getAuthenticationHeader and getServerUrl methods as deprecated

Since UR12, getServerUrl is deprecated, and getClientUrl is the recommended approach. To handle the transition, you might want to first check if getClientUrl is available and then use getServerUrl as a second option. This is to ensure backward compatibility, which is important for ISVs. You can see my implementation in the most recent of XrmSvcToolkit.

You may download the cheat sheet document from the following link on my Windows Live SkyDrive.



This may not worth a dedicated blog post, but I thought this is the only way to reach out to the community in order to have your attention.

Please let me know in case I have missed anything important.

Release: XrmSvcToolkit v0.2

It has been a while since the last update of XrmSvcToolkit.

Today, I just updated the library to a new version, v0.2, which includes the following two updates:

• Cross-browser support
• Bug fixes for synchronous calls that don't have any return values

So the library should work for CRM 2011 December 2012 Service Update or UR12. 

The new version is available for download at codeplex website. Please let me know if you run into any issues with the new version. 

Hope this helps. 

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.

• Business Intelligence Development Studio component from SQL Server 2012
• A server with SQL Server DQS installed (The DQS server can be installed on SQL Server itself it is a development environment, for production purpose, your DQS server might reside on a different box)
• SSIS Integration Toolkit for Microsoft Dynamics CRM
• Windows Identity Foundation

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.

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

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

Scenario Summary

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

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

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

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

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

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

Let's get started.

Prerequisites

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

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

Developing SSIS Data Flow

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

Summary Notes

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

Thanks for reading, hope this helps. 

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.

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

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.