This documentation applies to older versions of WSO2 ESB connectors. To find the documentation relevant to the version you are using, select the connector from the WSO2 Connector Store and click Documentation.

All docs This doc
Skip to end of metadata
Go to start of metadata

This page describes a connector scenario by integrating the Salesforce REST Connector with the Power Bi connector. For example you can use the Salesforce REST Connector to get the account details (Eg :- Id, Name, Account type of the records in your organization) from Salesforce and add that account details into table in the dataset of Power Bi using addRows operation in the Power Bi connector

 Overview

The following diagram illustrates the above use case.


  1. Get the account details from the Salesforce using query operation.
  2. Add the account details into the Power Bi using the addRows operation.

Environment setup for running the above use cases

Sample configuration

Following is a sample proxy service that illustrates how to test the above scenario.
Sample Proxy
<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse"
       name="importdataintoPowerbi"
       startOnLoad="true"
       statistics="disable"
       trace="disable"
       transports="http,https">
   <target>
      <inSequence>
         <property expression="json-eval($.salesforceRESTHostName)"
                   name="salesforceREST.hostName"/>
         <property expression="json-eval($.salesforceRESTRefreshToken)"
                   name="salesforceREST.refreshToken"/>
         <property expression="json-eval($.salesforceRESTClientSecret)"
                   name="salesforceREST.clientSecret"/>
         <property expression="json-eval($.salesforceRESTClientId)"
                   name="salesforceREST.clientId"/>
         <property expression="json-eval($.salesforceRESTAccessToken)"
                   name="salesforceREST.accessToken"/>
         <property expression="json-eval($.salesforceRESTQueryString)"
                   name="salesforceREST.queryString"/>
         <property expression="json-eval($.salesforceRESTApiVersion)"
                   name="salesforceREST.apiVersion"/>
         <property expression="json-eval($.salesforceRESTApiUrl)"
                   name="salesforceREST.apiUrl"/>
         <property expression="json-eval($.salesforceRESTIntervalTime)"
                   name="salesforceREST.intervalTime"/>
         <property expression="json-eval($.salesforceRESTRegistryPath)"
                   name="salesforceREST.registryPath"/>
         <property expression="json-eval($.powerbiAccessToken)" name="powerbi.accessToken"/>
         <property expression="json-eval($.powerbiRefreshToken)"
                   name="powerbi.refreshToken"/>
         <property expression="json-eval($.powerbiClientId)" name="powerbi.clientId"/>
         <property expression="json-eval($.powerbiClientSecret)"
                   name="powerbi.clientSecret"/>
         <property expression="json-eval($.powerbiApiUrl)" name="powerbi.apiUrl"/>
         <property expression="json-eval($.datasetId)" name="powerbi.datasetId"/>
         <property expression="json-eval($.tableName)" name="powerbi.tableName"/>
         <property name="fields" scope="operation" value="["/>
         <property name="length" scope="operation" value=""/>
         <salesforcerest.init>
            <apiVersion>{$ctx:salesforceREST.apiVersion}</apiVersion>
            <hostName>{$ctx:salesforceREST.hostName}</hostName>
            <accessToken>{$ctx:salesforceREST.accessToken}</accessToken>
            <refreshToken>{$ctx:salesforceREST.refreshToken}</refreshToken>
            <clientSecret>{$ctx:salesforceREST.clientSecret}</clientSecret>
            <clientId>{$ctx:salesforceREST.clientId}</clientId>
            <apiUrl>{$ctx:salesforceREST.apiUrl}</apiUrl>
            <intervalTime>{$ctx:salesforceREST.intervalTime}</intervalTime>
            <registryPath>{$ctx:salesforceREST.registryPath}</registryPath>
         </salesforcerest.init>
         <salesforcerest.query>
            <queryString>{$ctx:salesforceREST.queryString}</queryString>
         </salesforcerest.query>
         <property expression="//records" name="records"/>
         <iterate continueParent="true"
                  description=""
                  expression="//records"
                  id="records_iterator"
                  sequential="true">
            <target>
               <sequence>
                  <property expression="json-eval($.records)" name="records"/>
                  <property expression="get-property('operation','fields')" name="fields"/>
                  <script language="js">
							var records = mc.getProperty("records");                            
                            var obj = eval ("(" + records + ")");
                            var ans;
                            var fields = mc.getProperty("fields");
                            fields = fields.concat("{");                          
                            var Id = obj['Id'];
                            var Name = obj['Name'];
                            var AccountType = obj.attributes['type'];
                            if(Id !="" &amp;&amp; Id !=null &amp;&amp; Name !="" &amp;&amp; Name !=null){
                       			fields = fields.concat('"Id"' + ':"' + Id + '","Name"' + ':"' + Name + '","AccountType"' + ':"' + AccountType + '"');
                    		}
                            fields = fields.concat("},");
                			mc.setProperty("fields",fields);
                			mc.setProperty("length",fields.length());
				  </script>
                  <property expression="get-property('fields')" name="fields" scope="operation"/>
                  <property expression="get-property('length')" name="length" scope="operation"/>
               </sequence>
            </target>
         </iterate>
         <property expression="get-property('operation','fields')"
                   name="fields"
                   scope="operation"/>
         <property expression="get-property('operation','length')"
                   name="length"
                   scope="operation"/>
         <property expression="fn:concat(fn:substring(get-property('operation','fields'),0,get-property('operation','length')),']')"
                   name="values"/>
         <powerbi.init>
            <accessToken>{$ctx:powerbi.accessToken}</accessToken>
            <refreshToken>{$ctx:powerbi.refreshToken}</refreshToken>
            <clientId>{$ctx:powerbi.clientId}</clientId>
            <clientSecret>{$ctx:powerbi.clientSecret}</clientSecret>
            <apiUrl>{$ctx:powerbi.apiUrl}</apiUrl>
         </powerbi.init>
         <powerbi.addRows>
            <datasetId>{$ctx:powerbi.datasetId}</datasetId>
            <tableName>{$ctx:powerbi.tableName}</tableName>
            <rows>{$ctx:values}</rows>
         </powerbi.addRows>
         <property expression="$axis2:HTTP_SC" name="addRowsStatusCode"/>
         <filter regex="true"
                 source="get-property('addRowsStatusCode') != 200">
            <then>
               <log level="full"/>
               <property name="message" value="Unable to Insert the row "/>
               <loopback/>
            </then>
            <else>
               <log level="full"/>
               <property name="message" value="Success: Successfully Import the Data"/>
               <loopback/>
            </else>
         </filter>
         <respond/>
         <send/>
      </inSequence>
      <outSequence>
         <property name="messageType" scope="axis2" value="application/json"/>
         <payloadFactory media-type="json">
            <format>
                    {
                    "Response":{
                    "process":"ImportDataIntoPowerBi",
                    "activityResponse":"$1"
                    }
                    }
                </format>
            <args>
               <arg evaluator="xml" expression="get-property('message')"/>
            </args>
         </payloadFactory>
         <log/>
         <send/>
      </outSequence>
   </target>
   <description/>
</proxy>                        
Sample request
{
  "powerbiAccessToken": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6Ikk2b0J3NFZ6QkhPcWxlR3JWMkFKZEE1RW1YYyIsImtpZCI6Ikk2b0J3NFZ6QkhPcWxlR3JWMkFKZEE1RW1YYyJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvNDg1NWRjNDEtNmU4MS00YTdkLWJhOGYtZGM5NGNiMGI3NTE3LyIsImlhdCI6MTQ3OTAzMTg1MywibmJmIjoxNDc5MDMxODUzLCJleHAiOjE0NzkwMzU3NTMsImFjciI6IjEiLCJhbXIiOlsicHdkIl0sImFwcGlkIjoiNTAyMDgxM2QtOTgzMC00NDk0LWE4MjYtYTU3MDVhMmIxZTRhIiwiYXBwaWRhY3IiOiIxIiwiZmFtaWx5X25hbWUiOiJHbmFuZXN3YXJhbiIsImdpdmVuX25hbWUiOiJCaXJ1bnRoYSIsImlwYWRkciI6IjE3NS4xNTcuMjQ0LjIiLCJuYW1lIjoiQmlydW50aGEgR25hbmVzd2FyYW4iLCJvaWQiOiIzZWM1MDc0ZC1jOTVhLTRmZWQtOWEzZS1hNTM0YzE1ZjE5YzQiLCJwbGF0ZiI6IjE0IiwicHVpZCI6IjEwMDNCRkZEOUJGM0ZGQ0EiLCJzY3AiOiJEYXNoYm9hcmQuUmVhZC5BbGwgRGF0YXNldC5SZWFkV3JpdGUuQWxsIEdyb3VwLlJlYWQgUmVwb3J0LlJlYWQuQWxsIiwic3ViIjoidG9ZaTAteFNDT3hYSTV4eTB2QkZuWmdzTjc5N2hXV3l6YWxBQnJISVF0USIsInRpZCI6IjQ4NTVkYzQxLTZlODEtNGE3ZC1iYThmLWRjOTRjYjBiNzUxNyIsInVuaXF1ZV9uYW1lIjoiYmlydW50aGFAZ25hbmVzLm9ubWljcm9zb2Z0LmNvbSIsInVwbiI6ImJpcnVudGhhQGduYW5lcy5vbm1pY3Jvc29mdC5jb20iLCJ2ZXIiOiIxLjAiLCJ3aWRzIjpbIjYyZTkwMzk0LTY5ZjUtNDIzNy05MTkwLTAxMjE3NzE0NWUxMCJdfQ.f5HoxDyvi3EcjmKzUCzKobq1jjGECm68bfbGAn72SXOOwqYG9IkvzOuhR_4UL0BSp-E7wex9MqkrGUK0b0rJ1awg8kF7Y5ICjnIiuEHK7jLJf5DEEMb91tBRo3mMgBrri_UR7V0MIBG5IRMtbBiAIZCn-dZw9izCc3pOb9J3rx7ifMdda5frKqF3gQXUGl_814PXQia5EGxcTAfVcBbvcgTZ7FQjzidgI0qzoirNtZshrFzAqj7N1_2WpOlVC-XugA4Oi5vOcm2A3LHZYhYrk1GoqJga8liyUALWDyrKsvyPHGO9dEoYFVCgBK-48envJWlzOO987rI6MACWz8QmIg",
  "powerbiRefreshToken": "AQABAAAAAADRNYRQ3dhRSrm-4K-adpCJ0Bc0NMKTefb1M1UhZxzmA3v3afmAGpsz_3ISKe49U594Luh4vS53BoCWlAG0vlAv3y5gwhtln1smhbBM6-XZSjYvhgYrGMExiCnX92VPbqI61kYOnOrLtQ8MypdDMdoEotp0VIw3wMV-Pa9qU2J7DhPG0_r7V1b-ZqhQy0PZRRH7RTHelDLpCGsKHYNTqdgFH10GO8JOCHDwImXO5uMBgrxkI9hsAgYw-kc4HvSX8Ah8HXjfHGD3i90Rc79J0mA0eeL4knNkEKyBuXE7CVrEyFUKk6ijybCneHRUOC2_ejY2C04W3S4Us7TlC8rLOAo3Tz6JTF5gpTlmJ7QSF5k_7yiIT0ZmnloVfzCorkqxpcVX0rheQ3b83zX7I4VnEjsdUlIsvcV4QXPbQTcuNRsmEKZHErRph_8F-eG-gxUOBh4DWMss8FPylD8_tioscFIcMhC7aOXglOVnUNESFgJhcp8L0duu64Twn_9b1mCSlMXw8bM8ASm7QabHTnxXGmQHCGmehtd3Df9dIaAbXjwtwkxOSJjAlvGdjGoThO1IJ9gO5fMhaCRicfC8rkVQVsWaP_-sgeZtMFKyItT43C3CiWhPLQbKYwuOeJvWrHtw6z4A4oKPLJ-ALwMBDj8ffkiKjtdAHvpT5K3NQeVcMCkJnvkinLfSUo_--GSoxeWmJpktAhVIv8f8MB-TLUXc5DhufPUrZs1ofPTTZegzild9e-aob_K95zE_tzbvJa_cDF8gAA",
  "powerbiClientId": "5020813d-9830-4494-a826-a5705a2b1e4a",
  "powerbiClientSecret": "I+mhnYnSKqnsZmORLtPO5vbJCSnDRdP9J3xUyTPez24=",
  "powerbiApiUrl": "https://api.powerbi.com",
  "datasetId": "dd2defac-d062-4262-a9a9-00c1071723b8",
  "tableName":"Records",
  "salesforceRESTHostName": "https://login.salesforce.com",
  "salesforceRESTRefreshToken": "5Aep861TSESvWeug_xCxj8yFaTjeV0kXa8X.gg5d1ipArZn7qABXj_BUgpsSVDvRfMNJtYwqnie1JejvB_GrLnJ",
  "salesforceRESTClientSecret": "3543063387981089204",
  "salesforceRESTClientId": "3MVG9ZL0ppGP5UrDt0n2ptTJ_fWBXDlYYXTic9.ApotX3azPQhMX2dw_gXGQ1JyPwTLWjrnCB.0K3vpUJV3Jv",
  "salesforceRESTAccessToken": "00D28000000avoo!ARoAQGVqGhimwqewU5TNG1Uf4geWWRx9gV13sDffT1zXFHY_mAl158PI1wqfiAsH6uMOJfp5kzps0ERHXxTXpY8GWfNIltID",
  "salesforceRESTQueryString": "select id, name from Account limit 4",
  "salesforceRESTApiVersion": "v32.0",
  "salesforceRESTApiUrl": "https://ap2.salesforce.com",
  "salesforceRESTIntervalTime": "2400000",
  "salesforceRESTRegistryPath": "connectors/salesforcerest"
}

Configure above scenario using ESB Tooling

You can configure the above scenario with ESB tooling support that enables easy integration with just a few clicks and drag and drops.

  • No labels