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.