Monday, 9 September 2013

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.


5 comments:

  1. Hi,
    My CSV File does not have any ID.I mean the same ID created by default from MS-D CRM2011 when you create a form/entity.

    Question: should I use that kit (ssis-integration-toolkit-for-microsoft-dynamics-crm) without mapping the ID since it is not present in the CSV File?

    In this case I have to ignore the destionation ID in CRM2011, right?

    Thank you in davance

    ReplyDelete
  2. sorry for the late reply. you need the ID's on the CSV file you need to create columns to hold the ID's if they don't exist, foe e.g. When you run the SQL query you then have to map the output to columns in the CSV.

    You can't ignore the destination ID otherwise both KingsWaySoft or CozyRoc components would error.

    ReplyDelete
  3. Hi Nuno,

    Thank you for your posts! Quick question for you. Are you aware there is another product for SSIS which includes Dynamics CRM adapters? It is called COZYROC SSIS+ library and in fact is better value compared to Kingswaysoft. Hope you will find the time to evaluate it. When you test and develop from Visual Studio (BIDS), no license key is required. Cheers!

    ReplyDelete
    Replies
    1. Hi Cozyroc,
      Thanks for your feedback. I'm aware you will find on my other SSIS blog posts I use cozyroc as well.

      Regards
      Nuno

      Delete
    2. Hi Nuno,

      Thank you! Do you have preference of one product over another? I'll be happy to hear your recommendations for COZYROC.

      Delete