Wednesday, 4 December 2013
You find my posts helpful?
If you like to grab a coffee/tea and read some of my posts and you think they had value to the CRM community, please consider nominating me for MVP on the below link:
http://mvp.microsoft.com/en-us/nominate-an-mvp.aspx
My email address: nuno.m.costa@gmail.com
Thanks for your help.
Regards
Nuno Costa
Friday, 22 November 2013
Dynamics CRM 2013 exchange auto-discovery
I was troubleshooting the auto-discovery process in CRM 2013 server-side synchronization and built an interesting picture on the process, if you ever need to troubleshoot the exchange auto-discovery hope this article helps.
The below diagram illustrates the steps CRM asynchronous service takes to lookup an exchange server.
you can get more information about this on:
http://msdn.microsoft.com/en-us/library/ee332364(v=exchg.140).aspx
http://msdn.microsoft.com/en-us/library/ee160402(v=exchg.80).aspx
The below diagram illustrates the steps CRM asynchronous service takes to lookup an exchange server.
- Queries DNS for a LDAP server
- Queries LDAP for SCP pointers and SCP URLs
- LDAP returns the data and CRM connects via HTTPS to the relevant exchange server
For troubleshooting I'm using wirehark for network traffic capture. Before you start capturing packets in wireshark first enable one of the exchange profiles to use auto-discovery make sure you have one mailbox for testing and start capturing packets with wireshark. Now enable the mailbox this will trigger verification steps and you should see successful or failure messages in CRM when you get all 3 messages go to wireshark and stop the capture.
The first protocol we may want to look at is DNS so we want to know what CRM is querying to find out the Exchange server, on the search bar in wireshark type dns
below we see a number of things happening but the first thing I've noticed it's CRM querying for an SRV record of type _ldap
_ldap._tcp.London._sites.domain.com
The first protocol we may want to look at is DNS so we want to know what CRM is querying to find out the Exchange server, on the search bar in wireshark type dns
below we see a number of things happening but the first thing I've noticed it's CRM querying for an SRV record of type _ldap
_ldap._tcp.London._sites.domain.com
if we open the packet we can see the servers that are returned based on that query.
Fine, so lets see what's happening on the ldap side, type ldap on the search bar and you should see the following conversation:
There is quite a lot going on on the above packet capture, digging into a number of packages I come across the below traces which shows me CRM is looking up:
SCP pointers
SCP URLs
you can get more information about this on:
http://msdn.microsoft.com/en-us/library/ee332364(v=exchg.140).aspx
http://msdn.microsoft.com/en-us/library/ee160402(v=exchg.80).aspx
To test this I've copied the same filter on the packet captured with wireshark and fired the ldp tool and done the same search and also included the attributes listed on the captured packet: serviceBindingInformation and Keywords
(&(objectClass=serviceConnectionPoint)(|(keywords=67661d7F-8FC4-4fa7-BFAC-E1D7794C1F68)(keywords=77378F46-2C66-4aa9-A6A6-3E7A48B19596)))
if I run the above query I get the following results:
so we come up with the query needed to test the LDAP query CRM will perform to lookup the exchange server. After this all queries are HTTPS and we can't see what is going one.
Hope this helps.
Monday, 18 November 2013
Dynamics CRM 2013 Editing image permissions
I found that out-of-the-box the sales person security role cannot edit her/his image this seems to be a bug as the error screen does not allow you to navigate away from it unless you refresh the page. I've also found that after importing my organizations user's couldn't edit/upload images due to the same missing permission.
so if you try to edit your image with a sales person security role, you get the following message:
The only way to close this error message is to refresh IE. To allow users to edit their image make sure they have the below permission:
so if you try to edit your image with a sales person security role, you get the following message:
The only way to close this error message is to refresh IE. To allow users to edit their image make sure they have the below permission:
Friday, 8 November 2013
Dynamics CRM 2013 Configuring Exchange Syncronization
With Dynamics CRM 2013 the Asynchronous service now provides server-side Exchange Synchronization allowing users to:
In the past appointments could only be synchronized with the CRM Outlook client installed and configured, if you booked an appointment with multiple people the appointment would only appear in other users Outlook Calendar if those same users had the CRM client installed and synchronizing with CRM. With Exchange Synchronization you eliminate this need for the CRM client, appointments created by you that require multiple people get directly written to their exchange profile so you automatically get an appointment alert to accept or decline.
On this article I will walk-through how to set up Exchange synchronization with Dynamics CRM 2013, here is an overview of the steps:
1. Self-signed Certificate
To generate the certificate open IIS and highlight the server name and double click Certificates
On the right-hand side click on Self-signed certificate
Give it a Name (I call it MyCertificate) leave Personal and click OK
You should see the certificate listed on the list:
Next step is to bind the Dynamics CRM website to HTTPS (443)
Highlight the Dynamics CRM web site under sites and on the right-hand side chose Bindings. The below window opens and on the type field you choose HTTPS this will enable the SSL certificate field to choose a certificate and we choose the MyCertificate, click OK
2. Exchange Account Impersonation
Before we start Configuring profiles in CRM we want to make sure CRM service account has impersonation rights on the Exchange side to do this we run the following PowerShell command:
Exchange 2010
New-ManagementRoleAssignment -Name "" -Role:ApplicationImpersonation -User ""
For example: New-ManagementRoleAssignment -Name "CRM ExServiceAcc" -Role:ApplicationImpersonation -User crm13.service
Exchange 2007
3. Configuring CRM Exchange Profile
With the https binding in-place navigate to CRM using https://server/organization and settings > Email Configuration
Click on Email Server Profiles and then New Exchange Profile:
Fill in the details as per the below screenshot, you could use auto-discovery if it's working okay for you, I have tested in my environment and worked perfectly. The service account we specified has been given server-wide impersonation.
I've named the profile UK Mailboxes if you have users across different countries and exchange servers located on these sites you should create multiple profiles with the corresponding Exchange servers.
Leave the Advanced settings as they are:
Navigate to Mailboxes and on the Ribbon choose Add Existing Mailbox, by default every user created or imported during an organization import it's automatically generated a mailbox in the system, so when you click add existing mailbox the system will list all available user accounts.
4. Configuring Mailboxes
At this stage we have created an Email Server Profile and added 2 mailboxes, lets double click on one of the mailboxes:
Change the:
Server Profile: UK Mailboxes (the profile we just created)
Incoming Email: Server-Side Synchronization or Email Router
Outgoing Email: Server-Side Synchronization or Email Router
Note: on the configuration test results you should see Not Run instead of Success the screenshot was taken after the configuration was completed
Approve the Email and then click on Test & Enable Mailbox:
You then get the following alert message on the mailbox profile:
Click on Alerts and in a few seconds you will see the following messages coming up:
This means all checks completed successfully and you ready to test it in Outlook. If you look back at the Mailbox General tab you should now see the Configuration test results as Success.
During this test a test email is sent to your Inbox:
5. CRM Global Email settings
Before we start the Outlook test, lets first look at the CRM Global Email settings navigate to: Settings > Administration > System Settings
Go to the Email Tab, on this section there are a number of important settings that you should think from a design phase rather than implementation.
Configure email processing:
here you define which default method you want to process users email as it says if you choose Server-Side it will block Email Router. As we want to test server-side synchronization choose server-side instead of Email router.
Configure default synchronization method:
This will define users profile preferences and this is very important from a design perspective because will avoid duplicating work. If all your users are in UK you can default a server profile to UK Mailboxes, if you have users across different countries you could still define a default Email Server Profile using auto-discovery and then workout manually any specific users that require a manual Server Profile.
On the incoming, outgoing email and appointments, contacts and tasks default this to Server-Side synchronization or Email Router this will make sure it can use both.
Below you define if you should only process emails for approved users, this means if email processing is not a sensible subject in your implementation then I would recommend you untick this boxes because it removes an extra click on the configuration steps as we have seen above.
Configure email correlation, by default both tracking token and smart matching are enabled this is a business requirements rather than a performance or optimization issue. The tracking token will add a token CRM:Number to the emails subject which get processed by CRM and smart matching will be more transparent to users and attempt to use a matching pattern to find emails that should be tracked in CRM.
Note: Using a tracking token will be more efficient than smart matching but at a cost of being less transparent to users.
6. Test Synchronization
You ready to test the exchange synchronization create one appointment in CRM and include as required yourself and another CRM user fill in the other details and SAVE in a few minutes if all works well a meeting request is received in Outlook by all users.
To test sending emails confirm that on the Configure Email processing in the Global email settings is set to server-side synch, send an email to someone from CRM and ask that person to reply back, you should see the email tracked automatically in CRM.
7. Design Overview
On the design overview I just want to share a high-level diagram on the possible design routes for server-side synchronization.
The above diagram illustrates a possible scenario where there is a main office Lisbon and all other 3 branch offices have CRM users but out of the 3 only 2 offices use their own Exchange server. In CRM you define 3 Email Exchange Server Profiles:
The Default Exchange server profile it's applied to all users mailboxes so you only have to apply manually Exchange profiles on users mailboxes in London and New-York. Toquio and Lisbon will both default to the default Exchange profile which points at Lisbon.
8. Known issues
While testing the server-side synchronization I found that some mailboxes didn't get processed though the Test configuration results passed successfully. Enabling logging on the Asynchronous servers I found the below error; this has been logged with Microsoft and currently being investigated so if you do find yourself unable to test the server-side synchronization bear in mind this could apply to your mailbox create a new user with a brand new mailbox to test your CRM configuration.
When this happens you may see the following error message:
On the Asynchronous service logs we found:
9. Summary & References
Hope you have enjoyed this article please leave your feedback.
- Synchronize contacts, tasks and appointments
- Process outgoing emails
- Process incoming emails
- Automatically tracking emails based on Correlation rules (smart matching or/and tracking token)
All this without the need for the CRM Outlook client.
In the past appointments could only be synchronized with the CRM Outlook client installed and configured, if you booked an appointment with multiple people the appointment would only appear in other users Outlook Calendar if those same users had the CRM client installed and synchronizing with CRM. With Exchange Synchronization you eliminate this need for the CRM client, appointments created by you that require multiple people get directly written to their exchange profile so you automatically get an appointment alert to accept or decline.
On this article I will walk-through how to set up Exchange synchronization with Dynamics CRM 2013, here is an overview of the steps:
- Set up an IIS self-signed certificate
- Configure Exchange Impersonation
- Create and configure a CRM 2013 Exchange profile
- Configure User Mailboxes
- Configure CRM global Email Settings
- Test synchronization
- Design Overview
- known issues
- Summary and References
1. Self-signed Certificate
To generate the certificate open IIS and highlight the server name and double click Certificates
On the right-hand side click on Self-signed certificate
Give it a Name (I call it MyCertificate) leave Personal and click OK
You should see the certificate listed on the list:
Next step is to bind the Dynamics CRM website to HTTPS (443)
Highlight the Dynamics CRM web site under sites and on the right-hand side chose Bindings. The below window opens and on the type field you choose HTTPS this will enable the SSL certificate field to choose a certificate and we choose the MyCertificate, click OK
2. Exchange Account Impersonation
Before we start Configuring profiles in CRM we want to make sure CRM service account has impersonation rights on the Exchange side to do this we run the following PowerShell command:
Exchange 2010
New-ManagementRoleAssignment -Name "
For example: New-ManagementRoleAssignment -Name "CRM ExServiceAcc" -Role:ApplicationImpersonation -User crm13.service
Exchange 2007
Get-MailboxServer "" | Add-AdPermission -User "" -AccessRights ExtendedRight -ExtendedRights ms-Exch-EPI-May-Impersonate, ms-Exch-EPI-Impersonation
The second PowerShell command requires the Distinguished Name (DN) of the Microsoft Client Access Server (CAS).
To enable impersonation rights on a single Microsoft CAS server use the following command:
Get-ClientAccessServer -Identity "" | Add-AdPermission -User "" -ExtendedRights ms-Exch-EPI-Impersonation
3. Configuring CRM Exchange Profile
With the https binding in-place navigate to CRM using https://server/organization and settings > Email Configuration
Click on Email Server Profiles and then New Exchange Profile:
Fill in the details as per the below screenshot, you could use auto-discovery if it's working okay for you, I have tested in my environment and worked perfectly. The service account we specified has been given server-wide impersonation.
I've named the profile UK Mailboxes if you have users across different countries and exchange servers located on these sites you should create multiple profiles with the corresponding Exchange servers.
Leave the Advanced settings as they are:
Navigate to Mailboxes and on the Ribbon choose Add Existing Mailbox, by default every user created or imported during an organization import it's automatically generated a mailbox in the system, so when you click add existing mailbox the system will list all available user accounts.
4. Configuring Mailboxes
At this stage we have created an Email Server Profile and added 2 mailboxes, lets double click on one of the mailboxes:
Change the:
Server Profile: UK Mailboxes (the profile we just created)
Incoming Email: Server-Side Synchronization or Email Router
Outgoing Email: Server-Side Synchronization or Email Router
Note: on the configuration test results you should see Not Run instead of Success the screenshot was taken after the configuration was completed
Approve the Email and then click on Test & Enable Mailbox:
You then get the following alert message on the mailbox profile:
Click on Alerts and in a few seconds you will see the following messages coming up:
This means all checks completed successfully and you ready to test it in Outlook. If you look back at the Mailbox General tab you should now see the Configuration test results as Success.
During this test a test email is sent to your Inbox:
This is a test message to check the outgoing email
configuration for [User].
5. CRM Global Email settings
Before we start the Outlook test, lets first look at the CRM Global Email settings navigate to: Settings > Administration > System Settings
Go to the Email Tab, on this section there are a number of important settings that you should think from a design phase rather than implementation.
Configure email processing:
here you define which default method you want to process users email as it says if you choose Server-Side it will block Email Router. As we want to test server-side synchronization choose server-side instead of Email router.
Configure default synchronization method:
This will define users profile preferences and this is very important from a design perspective because will avoid duplicating work. If all your users are in UK you can default a server profile to UK Mailboxes, if you have users across different countries you could still define a default Email Server Profile using auto-discovery and then workout manually any specific users that require a manual Server Profile.
On the incoming, outgoing email and appointments, contacts and tasks default this to Server-Side synchronization or Email Router this will make sure it can use both.
Below you define if you should only process emails for approved users, this means if email processing is not a sensible subject in your implementation then I would recommend you untick this boxes because it removes an extra click on the configuration steps as we have seen above.
Configure email correlation, by default both tracking token and smart matching are enabled this is a business requirements rather than a performance or optimization issue. The tracking token will add a token CRM:Number to the emails subject which get processed by CRM and smart matching will be more transparent to users and attempt to use a matching pattern to find emails that should be tracked in CRM.
Note: Using a tracking token will be more efficient than smart matching but at a cost of being less transparent to users.
6. Test Synchronization
You ready to test the exchange synchronization create one appointment in CRM and include as required yourself and another CRM user fill in the other details and SAVE in a few minutes if all works well a meeting request is received in Outlook by all users.
To test sending emails confirm that on the Configure Email processing in the Global email settings is set to server-side synch, send an email to someone from CRM and ask that person to reply back, you should see the email tracked automatically in CRM.
7. Design Overview
On the design overview I just want to share a high-level diagram on the possible design routes for server-side synchronization.
The above diagram illustrates a possible scenario where there is a main office Lisbon and all other 3 branch offices have CRM users but out of the 3 only 2 offices use their own Exchange server. In CRM you define 3 Email Exchange Server Profiles:
- Default Exchange Profile configured with Lisbon Exchange server
- New-York CRM Profile configured with New-York Exchange Server
- London CRM Profile configured with a London Exchange server
The Default Exchange server profile it's applied to all users mailboxes so you only have to apply manually Exchange profiles on users mailboxes in London and New-York. Toquio and Lisbon will both default to the default Exchange profile which points at Lisbon.
8. Known issues
While testing the server-side synchronization I found that some mailboxes didn't get processed though the Test configuration results passed successfully. Enabling logging on the Asynchronous servers I found the below error; this has been logged with Microsoft and currently being investigated so if you do find yourself unable to test the server-side synchronization bear in mind this could apply to your mailbox create a new user with a brand new mailbox to test your CRM configuration.
When this happens you may see the following error message:
On the Asynchronous service logs we found:
>Exception occurred while retrieving folder data from
exchange server. Exception: Unhandled Exception: System.ArgumentNullException:
Value cannot be null.
Parameter name: s
Server stack trace:
at System.Convert.FromBase64String(String s)
at Microsoft.Exchange.WebServices.Data.MapiTypeConverter.<.cctor>b__14(String
s)
at
Microsoft.Exchange.WebServices.Data.MapiTypeConverterMapEntry.ConvertToValue(String
stringValue)
at
Microsoft.Exchange.WebServices.Data.ExtendedProperty.TryReadElementFromXml(EwsServiceXmlReader
reader)
at
Microsoft.Exchange.WebServices.Data.ComplexProperty.InternalLoadFromXml(EwsServiceXmlReader
reader, XmlNamespace xmlNamespace, String xmlElementName, Func`2 readAction)
at
Microsoft.Exchange.WebServices.Data.ComplexProperty.LoadFromXml(EwsServiceXmlReader
reader, XmlNamespace xmlNamespace, String xmlElementName)
at
Microsoft.Exchange.WebServices.Data.ExtendedPropertyCollection.LoadFromXml(EwsServiceXmlReader
reader, String localElementName)
at Microsoft.Exchange.WebServices.Data.ComplexPropertyDefinitionBase.InternalLoadFromXml(EwsServiceXmlReader
reader, PropertyBag propertyBag)
at
Microsoft.Exchange.WebServices.Data.ComplexPropertyDefinitionBase.LoadPropertyValueFromXml(EwsServiceXmlReader
reader, PropertyBag propertyBag)
at
Microsoft.Exchange.WebServices.Data.PropertyBag.LoadFromXml(EwsServiceXmlReader
reader, Boolean clear, PropertySet requestedPropertySet, Boolean
onlySummaryPropertiesRequested)
at
Microsoft.Exchange.WebServices.Data.EwsServiceXmlReader.ReadServiceObjectsCollectionFromXml[TServiceObject](XmlNamespace
collectionXmlNamespace, String collectionXmlElementName,
GetObjectInstanceDelegate`1 getObjectInstanceDelegate, Boolean
clearPropertyBag, PropertySet requestedPropertySet, Boolean
summaryPropertiesOnly)
at
Microsoft.Exchange.WebServices.Data.GetItemResponse.ReadElementsFromXml(EwsServiceXmlReader
reader)
at
Microsoft.Exchange.WebServices.Data.ServiceResponse.LoadFromXml(EwsServiceXmlReader
reader, String xmlElementName)
at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.ParseResponse(EwsServiceXmlReader
reader)
at
Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ReadResponse(EwsServiceXmlReader
ewsXmlReader)
at
Microsoft.Exchange.WebServices.Data.SimpleServiceRequestBase.ReadResponse(IEwsHttpWebResponse
response)
at
Microsoft.Exchange.WebServices.Data.ExchangeService.InternalLoadPropertiesForItems(IEnumerable`1
items, PropertySet propertySet, ServiceErrorHandling errorHandling)
at
Microsoft.Crm.Asynchronous.EmailConnector.MonitoredExchangeService.LoadPropertiesForItems(IEnumerable`1
items, PropertySet propertySet)
at
System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr
md, Object[] args, Object server, Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage
msg, IMessageSink replySink)
Exception rethrown at [0]:
at
System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg,
Boolean bProxyCase)
at
System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed,
MessageData& msgData)
at
Microsoft.Crm.Asynchronous.EmailConnector.ExchangeSyncSteps.LoadPropertiesForItemsStep.LoadPropertiesForItemsDelegate.EndInvoke(IAsyncResult
result)
at
Microsoft.Crm.Asynchronous.EmailConnector.ExchangeSyncSteps.LoadPropertiesForItemsStep.EndRequest(IAsyncResult
response)
at
Microsoft.Crm.Asynchronous.EmailConnector.ExchangeSyncSteps.ExchangeSyncAsyncRemoteStep`2.AfterCall()
9. Summary & References
Hope you have enjoyed this article please leave your feedback.
Tuesday, 8 October 2013
Dynamics CRM 2011 Rollup 15 available - CRM Outlook Client Improvements
I've published an article a few weeks ago on improving Outlook performance here:
http://quantusdynamics.blogspot.co.uk/2013/07/dynamics-crm-2011-outlook-client.html
I basically recommended upgrading to SQL CE 4 for better memory handling this is because Outlook 32bits is limited to 2GB of memory and this causes the application to crash or become very unstable.
With the latest Rollup 15 we have great news on CRM Outlook client improvements/stability this now natively upgrades to SQL CE 4 and introduces a number of memory handling features making Outlook client a lot more stable.
Quote from the Rollup 15 page:
Update Rollup 15 for Dynamics CRM 2011 provides the following improvements:
Rollup 15 it's available here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=722e9791-0d27-4939-ac15-0e05f254490a
http://quantusdynamics.blogspot.co.uk/2013/07/dynamics-crm-2011-outlook-client.html
I basically recommended upgrading to SQL CE 4 for better memory handling this is because Outlook 32bits is limited to 2GB of memory and this causes the application to crash or become very unstable.
With the latest Rollup 15 we have great news on CRM Outlook client improvements/stability this now natively upgrades to SQL CE 4 and introduces a number of memory handling features making Outlook client a lot more stable.
Quote from the Rollup 15 page:
Update Rollup 15 for Dynamics CRM 2011 provides the following improvements:
- It includes a new feature that is scheduled to be delivered with Microsoft Dynamics CRM 2013. This feature moves the CRM client-specific workload into its own process so that it no longer shares memory with the Microsoft Office Outlook process. This feature is also known as Process Isolation.
- It includes an upgrade to Microsoft SQL Server for Windows CE 4.0 for better memory management, better caching, and connection enhancements.
- It updates the CRM for Outlook configuration files to make the CRM for Outlook SDF files compatible with SQL Server for Windows CE 4.0.
- It materializes the Address Book Provider to reduce performance issues that are caused by large address books.
- It limits the amount of active open forms.
- It provides a MAPI Lock time-out.
- It hard codes a previous registry setting that prevented pinned views from pulling down information to local SQL CE cache. This new DisableMapiCaching setting defaults to a value of 1. For more information about the behavior of this setting, see Slow Performance When Pinning Views in Microsoft Dynamics CRM
Rollup 15 it's available here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=722e9791-0d27-4939-ac15-0e05f254490a
Friday, 4 October 2013
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. 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
On the below screenshot:
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
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.
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:
- Query an internet WSDL service to retrieve live exchange rates GBP vs EUR and store it as XML
- Extract the value using a XML task
- Create a local txt file using a script task
- 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.
Subscribe to:
Posts (Atom)