Salesforce - Retrieve Salesforce Entity Data by REST API

In my previous post I have shown how to retrieve Salesforce entity data through SOAP API. The Salesforce platform can also be accessed through REST API.

To get the Salesforce REST API works, the first thing we need to do is to create an app in Salesforce. Go to Setup page, on your left hand side, expand Create node under the Build section, then click Apps link.

Now on the main panel, there is a section named "Connected Apps", click the New button next to it.

On the next screen, firstly we need to fill in some basic information, as shown below

Then go to the API section, tick the checkbox "Enable OAuth Settings". Immediately there will be OAuth settings shown on the screen. To demonstrate the REST API, here I just make the callback URL as a test URL, and select full access as the authentication scope. In a real production environment, the access scope should be limited to specific account/group level.

Now once we save it, we should see the confirmation message as below

The activation of the app should not take too long, but meanwhile, we need to collect some information for the API coding task. Open the App and you should see the App details page like below. The consumer key and consumer secret are what we need for our coding task

Time to look at the coding side. In my Simpro post (part2) and Telstra post (here) I used webclient to post the message. Actually we can also use HttpClient to do the same task.

Assume what I want to do is to get a list of leads in Salesforce. The final output of the work looks like below.

Because we are going to use HttpClient, we need to add System.Net.Http into our reference list of the C# project.

Now the remaining coding is just like other REST API, obtain the access token, and submit the request, then receive the response. One thing could be important to you, is the version of your Salesforce when you build the query string. In a normal scenario, the version should not affect your query string. But it is always a good practise to check the version/document.

Code to access lead entity is shown below

     private async void btnLogin_Click(object sender, EventArgs e)  
     {  
       HttpClient authClient = new HttpClient();  
       string ConsumerKey = "<ConsumerKey>";  
       string ConsumerSecret = "<ConsumerSecret>";  
       string Url = @"https://login.salesforce.com/services/oauth2/token";  
       string username = "<username>";  
       string password = "<password>";  
       string token = "<token>";  
       string loginpassword = password + token;  
       HttpContent httpContent = new FormUrlEncodedContent(new Dictionary<string, string>  
         {  
           {"grant_type", "password"},  
           {"client_id", ConsumerKey},  
           {"client_secret", ConsumerSecret},  
           {"username", username},  
           {"password",loginpassword}  
         }  
       );  
       HttpRequestMessage Request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Post,  
         RequestUri = new Uri(Url),  
         Content = httpContent  
       };  
       var responsemessage = await authClient.SendAsync(Request);  
       var response = await responsemessage.Content.ReadAsStringAsync();  
       if (responsemessage.IsSuccessStatusCode)  
       {  
         var authToken = JsonConvert.DeserializeObject<object>(response);  
         string instance_url = ((JObject)authToken)["instance_url"].ToString();  
         string oauToken = ((JObject)authToken)["access_token"].ToString();  
         string sfQuery = instance_url + "/services/data/v34.0/query?q=SELECT+name+from+Lead";  
         HttpRequestMessage QueryRequest = new HttpRequestMessage(HttpMethod.Get, sfQuery);  
         QueryRequest.Headers.Add("Authorization", "Bearer " + oauToken);  
         var QueryResponse = await authClient.SendAsync(QueryRequest);  
         string QueryResponseResult = await QueryResponse.Content.ReadAsStringAsync();  
         JObject sfObject = JObject.Parse(QueryResponseResult);  
         JArray sfArray = (JArray)sfObject["records"];  
         foreach (JObject o in sfArray)  
         {  
           listBox1.Items.Add(o["Name"].ToString());  
         }  
       }  
       else  
       {  
         MessageBox.Show(response.ToString());  
       }  
     }  

TSQL - Use bitwise to simplify the design

This quick post is because recently one of my colleagues asked for a design suggestion.

We know that we need to follow relational database design principle, that is, normalise the object to its 3rd normalisation form.

But in real world, it is a common practise to have a lookup table to store small amount of records in a relational database design scenario, and it is a common practise to have a junk dimension to merge miscellaneous values in a dimension modelling scenario.

However instead of creating tables, we can use bitwise function to make the design even simpler, like below codes demostrated

use tempdb

--to demostrate how to use bitwise function
declare @Days table (WeekdayName varchar(50), WeekdayNbr int, WeekdayValue int, Selected bit)
declare @Selected int

insert into @Days
values
('Mon', 1, POWER(2, 1), 0),
('Tue', 2, POWER(2, 2), 0),
('Wed', 3, POWER(2, 3), 0),
('Thu', 4, POWER(2, 4), 0),
('Fri', 5, POWER(2, 5), 0),
('Sat', 6, POWER(2, 6), 0),
('Sun', 7, POWER(2, 7), 0)



--assume we want to run the job on Wed and Sun
update @Days set Selected = 1 where WeekdayName in ('wed', 'sun')


select * from @Days

--then the saved weekday value is sum of selected weekday value
select @Selected =  SUM(WeekdayValue) from @Days where Selected = 1
print @selected

--to get bitwise map, & to the weekday value
select @Selected & t.WeekdayValue, WeekdayName
from @Days t


This is not to say we do not need to follow relational database design principle. But when use it properly, the function could be much faster and easier for maintenance.

SSIS & Salesforce - Retrieve Salesforce Entity Data by SOAP API

As advertised, Salesforce is the world No. 1 on demand CRM platform. It is quite popular nowadays, across different sizes and areas of businesses. From our clients, the platform is being used by sport utilities, government agencies, energy retailers, and computer and service retailers.

Unfortunately it still has its own problems, for example, data quality problem. Due to the openess of the Salesforce platform, it is nearly impossible to control the data quality from its own features, at least from our clients, data report generated from Salesforce is in horrible quality, definitely cannot be used for reporting/analysis purpose.

So what we could do is extracting the raw data from Salesforce, and do some cleansing task, load into SQL Server database, a standard ETL procedure. However it is difficult to find a free plugin/component you can use in your SSIS to extract Salesforce data. So what I would like to do, is to show how to retrieve the data from sales force through its API. (Keep in mind there are many ways to access Salesforce data, here is only one of them)

To begin our work, we need to have the API. Log into the Salesforce platform,  go to the Setup page, on the left hand side, under the Build - Develop list view, you should see the API option.

Click this link, a list of WSDL options should be shown on the main panel. They are for different design purpose, as described on the page. What we need is the Enterprise WSDL, as it contains the most basic types we need in this example. Right click the link "Generate Enterprise WSDL", and save the file to your local folder.

Now before go any further, a quick notification about the WSDL. The downloaded WSDL can NOT be used in the project directly. If we add the WSDL as a web service reference into the project, as shown below

When you compile and run the project, you will get error message like below

In short it is because the ListViewRecord, this complex type defined in the WSDL does not contain a data type. It is fine for some languages, because the columns "ListViewRecordColumn" contains the data type definition. But from .Net, we have to explicitly identify the data type.

So we need to manually fix this problem: Open the downloaded WSDL file, locate the node "ListViewRecord" as shown below

Under the tag </sequence>, we need to declare its data type by adding

 <xsd:attribute name="tmp" type="xsd:string" />  

Then it should look like

Now the API definition part is done. We need to collect some other information before we go to SSIS part.

To access the Salesforce API, we need to provide our login email, password, and the token. To get the token we need to go to My Setting of the Salesforce platform, on the left hand side, expand options under Personal node, you should see the option "Reset My Security Token"

By click it, on the main panel of the page, you will see the warning message

Click the button "Reset Security Token", you will see the confirmation message like below, and a system email will shot into your associated email address.

Now we have all the information we need from Salesforce side. But before we go into SSIS, a quick tip may save your time: you may want to set password never expires under a Dev environment. Otherwise every time you change your password, you need to reset your security token.

Now time to look into SSIS. What I would like to do is to use a script component to get the User data from Salesforce.

Firstly drop a data flow task onto the control flow panel.

Within in the data flow task, drag and drop a script component, and select source on the pop up window.

Double click the script component, and go to "Inputs and Outputs" tab. As you can see, I created four output columns for the default output, FirstName, LastName, Title, and Email. They will be used to hold data retrieved from the User entity in Salesforce. For them, I defined their data type as string, length as 255. (You can also see I created another output "Output_Err". Because Salesforce's API provide the login result class, it can be used to show standard or customised error messages. But it will not be covered in this post, as it could be a huge topic)

Now to use the API, we need to add the downloaded, and modified WSDL as a web service into the script project.

Now turn off serialisation assembly option as shown below. Otherwise we will see "no binary code" error in the package.

At this stage, the project configuration is down. The only remaining job is coding.

Add the name space

Then I declared a SforceService to hold the actual service.

Coding is always simple from my understanding, once we know what we need to do

Then drag a Union All component under the Script component, and add a data viewer between these two components

Execute the task, the user records are exported from Salesforce

So now you have the chance to cleanse the Salesforce data, import into SQL Server, and do your own reporting/analysis task.


Codes within the CreateNewOutputRows method:

 public override void CreateNewOutputRows()  
   {  
     string email = "<login email>";  
     string password = "<login password>";  
     string token = "<security token>";  
     Service = new SforceService();  
     LoginResult LoginResult = Service.login(email, password + token);  
     string authurl = Service.Url;  
     Service.Url = LoginResult.serverUrl;  
     Service.SessionHeaderValue = new SessionHeader();  
     Service.SessionHeaderValue.sessionId = LoginResult.sessionId;  
     string sQuery = "SELECT FirstName, LastName, Title, Email FROM User";  
     QueryResult Qr = Service.query(sQuery);  
     if (Qr.size > 0)  
     {  
       foreach (sObject o in Qr.records)  
       {  
         if (o is User)  
         {  
           User u = (User)o;  
           Output0Buffer.AddRow();  
           Output0Buffer.FirstName = u.FirstName;  
           Output0Buffer.LastName = u.LastName;  
           Output0Buffer.Title = u.Title;  
           Output0Buffer.Email = u.Email;  
         }  
       }  
     }  
   }