Simpro & SSIS Part 1: Access Simpro through OAuth

Back to mid of 2014, one of our clients started to use a job management system, Simpro (Simpro.com.au), to run its business. On base of that, what they need is a data procedure to extract, transform, and report Simpro activities with other datasets, for example, contact center activities and dialler performance.

As there was no fast way to integrate a dialler into the system, we decided to download Simpro activities through its APIs. My basic idea is:

• Extract Simpro data into staging area via SSIS Script task
• Extract Dialler data into staging area via SSIS package, as it can be done through ADO.Net source
• Cleansing and load into production

Simpro is a market leading job management system. However you may have the same feeling like me if you look at its API documentation: they are not well prepared. And if you do the Google research, you do not have too many reference materials.

So this is the reason I write this series: to record what I did in the project, start from the planning, to end of the implementation. And I hope this series can help you, if you need to find some Simpro API references.

To access Simpro we need to use OAuth, which you can find quite a lot references online. And you can find the specific guideline for Simpro at http://api.simpro.co/. However they are not provided in .Net implementation. As I am not a coding expert, I used below two components in my design:

OAuthBase.cs

You can find it at https://oauth.googlecode.com/svn/code/csharp/OAuthBase.cs. As name suggested, it is the base to implement OAuth access in .Net code.

Updated on 02/10/2017, please use below link to get OAuthBase:
https://gist.github.com/tsupo/112124

Json.Net

An optional but handy component to parse JSON string. You can find it at http://james.newtonking.com/json.

So too much talk, let’s look at some codes. Below codes are used to do a query against Simpro's Customer entitty. Hope it gives you some ideas before my next post: SSIS script task for Simpro.

(note, it is my understanding that in most Simpro APIs CompanyID is the only parameter required)

 public string GetJasonString(string ConsumerKey, string ConsumerSecret, string Url)  
     {  
       string JasonBuffer = "";  
       //build signiture  
       OAuthBase oAuth = new OAuthBase();  
       string nonce = oAuth.GenerateNonce();  
       string timeStamp = oAuth.GenerateTimeStamp();  
       string normalisedUrl;  
       string normalisedRequestParams;  
       string signature = oAuth.GenerateSignature(new Uri(Url), ConsumerKey, ConsumerSecret, null, null, "POST", timeStamp, nonce, out normalisedUrl, out normalisedRequestParams);  
       signature = HttpUtility.UrlEncode(signature);  
       //parameter  
       StringBuilder sb = new StringBuilder("");  
       sb.AppendFormat("OAuth oauth_version=\"1.0\",");  
       sb.AppendFormat("oauth_nonce={0},", nonce);  
       sb.AppendFormat("oauth_timestamp={0},", timeStamp);  
       sb.AppendFormat("oauth_consumer_key={0},", ConsumerKey);  
       sb.AppendFormat("oauth_signature_method=\"HMAC-SHA1\",");  
       sb.AppendFormat("oauth_signature={0}", signature);  
       WebClient Client = new WebClient();  
       Client.Headers.Add("Authorization", sb.ToString());  
       Client.Headers.Add("Content-Type", "application/json");  
       Client.Headers.Add("Accept", "application/json");  
       Client.Proxy = null;  
       JavaScriptSerializer JSS = new JavaScriptSerializer();        
       Dictionary<string, object> Params = new Dictionary<string, object>();  
       //CustomerSearch parameters  
       Params.Add("CompanyID", 0);  
       Params.Add("Search", "%");  
       Params.Add("Limit", 500);  
       Params.Add("Offset", 2000);  
       string Command = JSS.Serialize(new Dictionary<string, object>  
       {  
         {"id", "1"},  
         {"method", "CustomerSearch"},  
         {"params", Params}  
       });  
       byte[] bytes = Encoding.UTF8.GetBytes(Command);  
       byte[] bytesbuffer = Client.UploadData(Url, "POST", bytes);  
       JasonBuffer = Encoding.Default.GetString(bytesbuffer);  
       return JasonBuffer;  
     }