{"id":1881,"date":"2015-10-27T18:52:48","date_gmt":"2015-10-27T13:22:48","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=1881"},"modified":"2015-10-27T18:52:48","modified_gmt":"2015-10-27T13:22:48","slug":"load-data-from-web-service-to-dynamics-crm-using-ssis","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2015\/10\/load-data-from-web-service-to-dynamics-crm-using-ssis\/","title":{"rendered":"Load Data from Web Service to Dynamics CRM using SSIS"},"content":{"rendered":"

Introduction: <\/strong><\/span><\/p>\n

Sometimes there are cases where we need to make a call to web service, from where we get data and save that data to Dynamics CRM entity. The response which we get from web service is in xml form. In one of our recent post we explained about\u00a0Iterating and Processing Records in Dynamics CRM using SSIS<\/a>.<\/p>\n

Also see :\u00a0<\/a>Analytics in Dynamics CRM\u00a0now has a new name – Maplytics<\/a><\/em><\/strong><\/span><\/p>\n

Walkthrough:<\/strong><\/span><\/p>\n

Here let\u2019s check out the whole process step by step.<\/p>\n

Create a new SSIS package. Here we will take the free global weather returning web service for example. Add the web service control from the SSIS toolbox and rename it to the web service which you will be consuming as follows:-\"Load<\/a><\/p>\n

Here I have named the web service control as Get Global Weather Service. Right click on the web service control \u00e0 Edit. Add the Service connection string as follows and set it to the HTTPConnections in Connection tab as you can see highlighted below :-\"SSIS\"<\/a><\/p>\n

Now you need to download the WSDL file for which you need to create a empty .wsdl file and point its location to the WSDLFile as you can see in below image:\"web<\/a><\/p>\n

Set the OverwriteWSDL File property to True and click on the Download WSDL button which will download the WSDL file for the webservice pointed at the specified location.<\/p>\n

Now move on to next tab i.e. Input.\"Load<\/a><\/p>\n

In above image you can see, as soon as we switch to Input tab in the Service property we start to get the GlobalWeather service to choose from dropdown. In the Methods property we get all the methods that are provided by the GlobalWeather webservice. Here in this example we have taken GetWeather method. As soon as we select the method we get the input parameters that are exposed by the service method. Here we can see two parameters viz. CityName and CountryName. You can directly set the values for these input parameters or you can set the variables through which you can set its value as you can see in above image. Also check the Variable checkbox and you get the option to set the value for variables using package variables. Here I have created two package variables and set them as input parameters.<\/p>\n

Then we move on to the next tab i.e. Output.\"Using<\/a><\/p>\n

In OutputType we can either set it as Variables where the response output from webservice will be stored in a variable we define as we have taken or else we can set it to file in which case the response will be written to the file.<\/p>\n

After all the settings are done for the web service control click OK and the web service is set up.<\/p>\n

Let\u2019s declare following variables which will be used later.\"SSIS\"<\/a>Next we need to remove the library references if any which are returned in the web service response or else we will not be able to extract the exact data. To do this we add a sequence container and connect it to the web service control. Then we add the XML Task control to process the library reference and add get the data. In below image you can find that :-\"SSIS<\/a><\/p>\n

For removing the library reference we add the XML Task control and rename it to \u201cRemove Library reference from XML\u201d and right click –> Edit and set the following properties.\"SSIS\"<\/a><\/p>\n

As you can see in the above image we need to set the Operation type as XSLT. Set the SourceType as Variable as we are saving our service response in variable. Set the Source to the variable in which the service response was set.<\/p>\n

Set the SaveOperationResult to True and Set the variable where you want to save after the XSLT operation is performed. We set the output to the same \u201cGlobalVariableResponse\u201d variable. Set the secondOperandType to variable and specify the SecondOperand as the variable (removeXMLLibXSLT). Here in this variable we have stored the XSLT that will remove the namespaces from XML.<\/p>\n

\u00a0Note: You can get this XSLT to remove namespace on net.<\/p>\n

\u00a0<xsl:stylesheet xmlns:xsl =”http:\/\/www.w3.org\/1999\/XSL\/Transform” version =”1.0″ ><\/p>\n

\u00a0 <xsl:template match =”@*” ><\/p>\n

\u00a0\u00a0\u00a0 <xsl:attribute name =”{local-name()}” ><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 <xsl:value-of select =”.” \/><\/p>\n

\u00a0\u00a0\u00a0 <\/xsl:attribute><\/p>\n

\u00a0\u00a0\u00a0 <xsl:apply-templates\/><\/p>\n

\u00a0 <\/xsl:template><\/p>\n

\u00a0 <xsl:template match =”*” ><\/p>\n

\u00a0\u00a0\u00a0 <xsl:element name =”{local-name()}” ><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 <xsl:apply-templates select =”@* | node()” \/><\/p>\n

\u00a0 \u00a0\u00a0<\/xsl:element><\/p>\n

\u00a0 <\/xsl:template><\/p>\n

<\/xsl:stylesheet><\/p>\n

After the library reference is removed if further want to process the response and start from any particular node of XML we can do that by adding a script component as we have done in our case.<\/p>\n

Next we want to extract the data from response. In our example we provided CountryName = \u201cUnited States\u201d and cityName = \u201cNew York\u201d to get the weather report of New York city. And the XML response returned is something like this \u2013<\/p>\n

<string\u00a0xmlns=”http:\/\/www.webserviceX.NET”><\/p>\n

<?xml version=”1.0″ encoding=”utf-16″?> <CurrentWeather> <Location>NEW YORK LA GUARDIA AIRPORT , NY, United States (KLGA) 40-47N 73-53W 11M<\/Location> <Time>Oct 25, 2015 – 09:51 AM EDT \/ 2015.10.25 1351 UTC<\/Time> <Wind> from the SW (220 degrees) at 9 MPH (8 KT):0<\/Wind> <Visibility> 7 mile(s):0<\/Visibility> <SkyConditions> overcast<\/SkyConditions> <Temperature> 57.0 F (13.9 C)<\/Temperature> <DewPoint> 53.1 F (11.7 C)<\/DewPoint> <RelativeHumidity> 86%<\/RelativeHumidity> <Pressure> 30.08 in. Hg (1018 hPa)<\/Pressure> <Status>Success<\/Status> <\/CurrentWeather><\/p>\n

<\/string><\/p>\n

We want to get Location, Time, Visibility and Temprature. We can do this by using XPATH method. As you can see in below image we add XML task and set XPATH in it as follows:-\"SSIS\"<\/a><\/p>\n

Here we set OperationType as XPATH. SourceType as variable to point to the XML response variable. We specify the SaveOperationResult to True and store the OperationResult in the respective variables. Now to specify XPATH we set the SecondOperandType as Direct Input. And in SecondOperand we give the XPATH. In XPath Options we set the PutResultInOneNode to False and set XPathOperations to values.<\/p>\n

Thus we set the XPATh for all the fields from XML response which we want to retrieve as you can see highlighted in below image :-\"SSIS<\/a><\/p>\n

Next we add the DataFlowControl and move the data stored from XPATH to CRM fields as follows \u2013\"Data<\/a><\/p>\n

We take script component where we get the data stored in the variables from XPATH operation and set it to the script Output which is then provided as input to the Dynamic CRM Source control and create a CityWeather entity record.<\/p>\n

Note : Here we have used Kingswaysoft SSIS Integration Toolkit for Dynamics CRM.<\/p>\n

Conclusion:<\/strong><\/span><\/p>\n

Thus we can get data from Webservice and move it to Dynamics CRM smoothly.<\/p>\n

Before you move to the next post, have you seen our new\u00a0Click2Export<\/a>\u00a0Solution? A 1 click solution to export reports to Word\/Excel and Pdf. Email us on\u00a0crm@inogic.com<\/a>\u00a0for a trial or if you would like to see a live demo.<\/p>\n","protected":false},"excerpt":{"rendered":"

Introduction: Sometimes there are cases where we need to make a call to web service, from where we get data and save that data to Dynamics CRM entity. The response which we get from web service is in xml form. In one of our recent post we explained about\u00a0Iterating and Processing Records in Dynamics CRM\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[19,24,31,57],"tags":[592,998,1654],"class_list":["post-1881","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm","category-dynamics-crm-2016","category-integrations-dynamics-crm","category-ssis","tag-dynamics-crm","tag-load-data-from-web-service","tag-ssis"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/1881","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=1881"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/1881\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=1881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=1881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=1881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}