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.