Dynamics CRM 2011 Updating Currencies with SSIS

I continue my voyage on SSIS for CRM packages and I find this solution I'm sharing really interesting because you can void all those javascript/plugin code overheads to get currencies updated with real markets exchange rates.

By using this SSIS package you can get currencies updated with live markets and at the same time running it from any SQL server you wish, without directly affecting the CRM SQL server performance.

Overview of the package:

1. Query an internet WSDL service to retrieve live exchange rates GBP vs EUR and store it as XML
2. Extract the value using a XML task
3. Create a local txt file using a script task
4. Update CRM

1. Setting Up the Web Service Task

On the HTTP Connection manager you need to specify the URL to connect to, I'm using the following WSDL server:
http://www.webservicex.net/currencyconvertor.asmx?WSDL

On the below screenshot select

• WSDLFile: You can create a empty wsdl file e.g. currency.wsdl
• set OverwriteWSDLFile to true
• click on the button: Download WSDL 

On the below screenshot:

• Service: select CurrencyConvertor
• Method: ConversionRate

Then select from the list the currencies you want to retrieve:

On the output you need to define a xml output file, create an empty file e.g. currency.xml

 2. XML task

Using an XML task we extract only the values and store it into a variable type object. Use the below configuration:

3. Retrieving Currency CRM GUID

We need a new Data Flow task to use the kings ways software CRM component to retrieve the 'transactioncurrencyid' using FetchXML and then store it in a variable type object using the Recordset Destination task

4. For each loop container
As we storing the variable as an array of objects I'm using a foreach loop to retrieve the content of each index.

5. Script Task

The script it's essentially building a flat .txt file which will store the relevant information to be used later as the input columns when updating CRM via the Kings way software component.

Select the variables we created above on the read only variables field, when you done click 'Edit Script'

The following code it's creating a .txt file on the C:\ drive with the variables information:

6. Update CRM
We load the flat file confirm we have an exchange rate column and the CRM GUID and link it to the KingsWay Software component to update CRM using the API.

And it's done!

Conclusion
Hope you enjoyed the concept it's really that easy and straight forward. You can deploy this package on any SQL server that has the SSIS component installed, you don't need to install it on your CRM SQL server and this is a great advantage because you not impacting performance on your CRM SQL server.

Schedule the package to run on a specific interval as per your requirements and voilà you get your exchange rates matching daily market rates.

Dynamics CRM 2011 Sorting Options sets alphabetically

I was recently working with a custom options set solution which renders options sets as multi select picklists, the solution is here:
http://blog.slalom.com/2013/01/23/multi-select-option-sets-part-2/

I came across a rather unpleasant reality on CRM options sets which is doing the right thing however causing a bit of a maintenance overhead if you want to extend CRM into other areas.

if we retrieve an option set with the below code:
returns an array of objects which may look like this:

Notice the sorting is done on the index value. Imagine you have 50 or more entries in one option set and once in a while you need to add a new entry and remove others, are you manually moving all index values to sort the option set alphabetically? if you have another entry starting with letter "A" then you need to change all none "A" to + 1 in the index value.
This would be painful and time consuming. I'm sharing a small piece of code that can do this for you if you face the same issue:

The results would then be sorted alphabetically:

Notice the index values now are not sequential and the text entries are alphabetically sorted.

Hope this was helpful, please leave your feedback.

Dynamics CRM 2011 updating user settings using SSIS

Have you ever had the need to set user preferences in bulk and you are unable to do so? This article will show how you could use SSIS to update user preferences in bulk.

On this tutorial I will be changing users currency settings to EUR. The following is an overview of the process: This time I'm using the CRM SSIS component from
http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm

1. Export users to Excel
1. will give an example how we could also use a SQL query to avoid the use of Excel
2. Get the CurrencyId using FetchXml and populate this data into Excel
3. Use the Excel as a data source  to update CRM user settings

1. Export Users to Excel [data_source]
First step is to export the users you want to update the currency into Excel to be used later as a data source for the final update.

I create an excel file with two column headers transactioncurrencyid and systemuserid.
I then use the SSIS CRM component to run the a FetchXml query and populate the excel file.

Note: It's on this particular FetchXML query that you define what users you want to export to later on update their currency settings, you could filter by their address_city, country etc...

and you should end up with something like this:

1.1 Using SQL Queries
you can bypass the use of Excel and go straight to source to destination without the time consuming excel process:

We use the OLE DB Source component to connect to our database and run the query we specify below in the screenshot:

Note: we are using Filtered Views this is a supported query and you should be using Filtered views as possible.

if you look at the columns mappings we are good to go:

we would end up with only one data flow process the first of the 3 data flow processes below. I've included the other 2 so we could look at the image and compare what happens with the Excel as a data source and with the SQL query as a data source.

Using SQL queries reduces the overhead of excel and is a quicker process, however Excel has the advantage of sharing work with multiple colleagues and mapping data.


2. Get EUR currency ID
To get the Euros currency ID we will use the FetchXML Builder tool

We copy the transactioncurrencyid from the results above onto the excel spreadsheet and you should have something like this:

3. Update CRM user settings with SSIS
Using the Excel file as a data source we input this data into the KingsWaySoft CRM SSIS destination component and update those CRM users in the spreadsheet

In the columns section make sure you map the two fields to the correct columns, transactioncurrencyid and systemuserid

Conclusion
We looked at how to use SSIS to update user preferences in bulk, looked at using Excel as a data source and how to build SQL queries to speed up the update process.

I hope this was helpful and please leave your feedback.

Dynamics CRM 2011 Optimize built-in maintenance jobs

I've noticed the maintenance job "Cleanup Workflows" was taking considerably more time to execute as my AsyncOperationBase table grows after some investigation I've noticed CRM needed a tweak to optimize this maintenance job.

The "Cleanup Workflows" maintenance job like the other CRM maintenance jobs are based on SQL storage procedures that are executed by the Asynchronous Maintenance service on a scheduled basis. This particular maintenance job was slowing down on this particular storage procedure: p_CleanupInactiveWorkflowAssemblies

which looks like this:


There is a select with a where clause on the AyncOperationBase table

This select takes around 3min o execute on my live environment (this was a lot longer on my uat and dev systems) with 3.5million records which I think is not that many records, this is because the column OwningExtensionId and OperationType are not indexed, out-of-the-box and up to RU14 it does not create the necessary indexes to make this query run faster you have to create it yourself.

I've created the following index:

and the query takes now 0seconds to be executed.

Hope this was helpful.

Dynamics CRM 2011 Outlook Client Optimization

With the introduction of CRM rollups e.g. RU12 with cross browser support I've noticed the CRM Outlook client is struggling and crashing more often possibly due to all the new changes in the new rollups a number of tweaks and optimizations are available to improve the user experience when using the CRM outlook client.

Making CRM 2011 Outlook client stable
I've noticed Outlook clients started to crash randomly after the deployment of RU11 analysing Outlook.exe crash dumps revealed the issue was on SQL CE 3.0. A new version of SQL compact edition is available version 4.0 which as documented, handles memory better eliminating (or near) the out-of-memory crashes in Outlook while working with the CRM Outlook client.

The following Microsoft KB article documents how to upgrade SQL CE 4.0:
http://support.microsoft.com/kb/2616319

The process as per KB article:

1. Install SQL CE 4.0
2. Delete the CRM 2011 client current configuration and delete any cashed databases
1. C:\Users\[user]\AppData\Local\Microsoft\MSCRM
1. delete all *.sdf files
3. EDIT the configuration wizard config file to load the SQL CE 4
4. EDIT the  CRM client startup config file to load new assemblies.
5. Open Outlook and confirm the new SQL CE is loaded as per below screenshot

However I found that on random machines the configuration wizard failed to connect to CRM, the reason behind this was related with a configuration line on the configuration wizard config file:

<system.net>
    <defaultProxy useDefaultCredentials="true" />
  </system.net>

Removing the above code allowed the connection to be made. If you don't want to remove the above code from every user the following hotfix will resolve the issue:

http://support.microsoft.com/kb/2580188

CRM 2011 client x64
If you have the opportunity to instead of 32bits use the 64bits version of Office, then it would be strongly recommended to use the x64 it adds extra stability to Outlook and CRM client, as 64 bits has more room for memory allocation.

  Registry Keys
 As per the following KB http://support.microsoft.com/kb/2585157 the below registry keys will improve performance:

HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient NotificationPollInterval set to 3600000 (Decimal) StateManagerPollInterval set to 10 (Decimal) ActiveCachesUpdatingPeriodMilliseconds set to 3000000 (Decimal) IncrementalDataCachesInclusionUpdatingPeriodMilliseconds set to 6000000 (Decimal) IncrementalDataCachesExclusionUpdatingPeriodMilliseconds set to 6000000 (Decimal HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient\{ORGGUID} TagPollingPeriod set to 600000 (Decimal) TagMaxAggressiveCycles 0

A couple of registry keys to consider as well:

HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient

DisableMapiCaching	1
AddressBookMaterializedViewEnabled	1

CRM Settings

From CRM settings > administration there is a few settings that you should also consider to improve user experience:

System Settings > Outlook Tab you want to increasing the default values to reduce overhead on the server and on the user side:

Privacy Settings > Error Reporting here you can configure centrally all users settings on how to deal with Microsoft errors, the advantage here is that you reduce the noise on the user side.

Conclusion
I've covered a few known optimizations, the most impact is SQL compact edition 4 so I strongly recommend you upgrading your outlook client deployment. I hope this was helpful please leave any questions or feedback.

Dynamics CRM 2011 using SSIS to monitor pending emails

On this article I'll walk through how to build a simple and fully supported SSIS package to track pending emails in CRM. The idea is to receive an alert email when we reach a threshold suggesting email router is stuck, slow performance or unusual mail activity in CRM.

On this tutorial I use the SSIS CozyRoc component to connect to Dynamics CRM, you can download it here:
http://www.cozyroc.com/products

The package process:

1. Connect to a CRM organisation using the API
2. Run FetchXML to retrieve all pending emails
3. Count how many emails pending and storing the total number in a variable
4. Use an IF THEN ELSE statement to set the flow of the package to successful or failure based on the total number of emails pending
5. send an alert email if successful or failure.

In visual studio go to: New > Project > Integration Services Project

The first step is to create a variable that will hold the total number of emails within the Control Flow tab select view and variables, this will display the variables screen on the left, click new variable and name it: RowCount, the scope should be set to 'Package' this means our variable is a global variable so it can be used either in the 'Data Flow' or 'Control Flow' area.

The next step is to create a connection to CRM to retrieve the data we want.

On the connection Managers diaglog box, right click and select New Connection. From the list choose DYNAMICS-CRM CozyRoc

The following screen should appear, configure it with your CRM information:

I renamed my connection as SandBox Connection as per below screenshot:

Next Step is to build your Data Flow tasks, click on the Data Flow tab and drag the Dynamics CRM source task on to the Data Flow area. 

Note: the Dynamics CRM Source should be available on the bottom of your Toolbox, if you have installed the CozyRoc Component and you don't see it there is because you didn't complete the post-install steps, you need to add those two tasks to the General section of the Toolbox

Also drag the Row counter task and the Data Flow area will look like this:

Note: we haven't linked the Dynamics CRM source with the row counter task we will do this soon.

Double click the Dynamics CRM source task and the following window appears:

On the above screenshot the first tab, you need to select the Connection Manager, on this case we select our SandBox Connection

Click on the Second Tab:

On the above screenshot we want to make use of the FetchXML feature, so at the bottom under 'Custom Properties' on the 'InputType' change entity to FetchXML and under 'FetchXML' paste the following XML:

Click OK all done on the 'Dynamics CRM Source' task. The next step is the 'Row Count' task link the 'Dynamics CRM source' to the 'Row Count' task and double click the 'Row Count' task the following screen appears:

Here we simply set the 'VariableName' to use our global variable, on the variable selection window you should see User::RowCount tick the box and click okay. 

At this stage we run the package it will:

1. Connect to CRM Organisation SandBox
2. Run the FetchXML
3. Pass the data to the Row Count task and store the total on the User::RowCount variable

Great so we the Data Flow process in place, now we need to Send an email if we have more than 100 Emails pending we want our Control Flow area to look like this:

At the above screenshot, we added a 'Script Task' task to process the data coming from the Data Flow, we then send an email either if the package runs successful or fails.

Double Click the Script Task and on the main screen ReadOnlyVariables select our variable User::RowCount

You should see this:

On the Script Task what we need to do now is to insert code to check if the variable is bigger than X if yes set to Successful completion or to Failure. On the same window click on The button at the bottom Edit Script and insert the following code under the Main() function:

The last step is to configure the 'Send Email' task to send an email in case of Successful or Failure. In this example I've added both routes (successful and Failure, however in my production environment I only have one route for when the email is bigger than X, if you understood the above code you should be able to decide how you want to apply your logic the two routes were added to this article to understand better the process flow of this package and for testing purposes you may want to receive an email saying Yes or No to confirm the package is running okay.

I hope this was useful, please leave your feedback.