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.