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
- Export users to Excel
- will give an example how we could also use a SQL query to avoid the use of Excel
- Get the CurrencyId using FetchXml and populate this data into Excel
- Use the Excel as a data source to update CRM user settings
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
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.