Simpro & SSIS Part 2: SSIS for Simple Entity

In this post I am going to discuss how I design the ETL procedure via SSIS.

Usually when I need to start a new project, I would like to have an overall understanding firstly, then do a quick coding demonstration, and finally turn the demonstration into implementations, as demonstration sometimes cannot be converted into a successful implementation.

Now go back to this Simpro topic

• The client required to merge data from two data sources (Simpro and the dialler) for reporting purpose
• From business perspective, Employees, Products, Quotes, Jobs, Invoices, and Payments are key entities I need to work on
• I had built a demonstration in prior post to show how to use OAuth to access Simpro, and how to make a Simpro API call

So I know I can start to turn the demonstration into implementation. Below is a simple implementation for Employee entity  

EMPLOYEE

The Employee information is retrieved through EmployeeSearch method. Due to the business process, I need to add some extra conditions to filter the result set, which is done by calling CostCentreSearch. But basically they follow the same routine.

To prepare the backend, I create an Employee table by using below script

CREATE TABLE Simpro.Employee

(

EmployeeKey INT IDENTITY(1, 1),

EmployeeID VARCHAR(50) NOT NULL,

EmployeeName VARCHAR(255) NOT NULL,

CreatedOn SMALLDATETIME NOT NULL DEFAULT(GETDATE()),

ModifiedOn SMALLDATETIME NULL,

CONSTRAINT PK_SimproEmployee PRIMARY KEY (EmployeeKey)

)

GO

Below is a control flow overview for the Employee Entity

Inside the data flow “DFT Simpro EmployeeSearch”:

And inside the data flow “DFT Simpro CostCentreSearch”

 
In the data flow design above, I use the script component as the data source to call the Simpro API. To do that, I firstly add output columns as demonstrated below

 Now we need to make the script work. Open the VSTA interface by click Edit Script, and add the references as mentioned in my prior post (OAuthBase.cs and Newtonsoft.Jason).

 

Within the method CreateNewOutputRows, I input below codes:

 

 public override void CreateNewOutputRows()  
   {  
     string consumerKey = "Your Consumer Key";  
     string consumerSecret = "Your Secret";  
     string url = @"http://URL/api/?format=json";  
     //build Signature  
     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>();  
     Params.Add("CompanyID", 0);  
     Params.Add("Limit", 65535); //be careful here  
     string Command = JSS.Serialize(new Dictionary<string, object>  
       {  
         {"id", "1"},  
         {"method", "EmployeeSearch"},  
         {"params", Params}  
       }  
         );  
     byte[] bytes = Encoding.UTF8.GetBytes(Command);  
     byte[] bytesbuffer = Client.UploadData(url, "POST", bytes);  
     string buffer = Encoding.Default.GetString(bytesbuffer);  
     //we do not need to deserialize all fields, just extract fields we need  
     JObject jObject = JObject.Parse(buffer);  
     JArray Results = (JArray)jObject["result"];  
     foreach (JObject Result in Results)  
     {  
       EmployeeSearchBuffer.AddRow();  
       EmployeeSearchBuffer.EmployeeID = (string)Result["EmployeeID"];  
       EmployeeSearchBuffer.EmployeeName = (string)Result["EmployeeName"];  
     }  
   }   

 

 

Codes above will give us a list of Simpro employees, but to ensure we load only what we need to load, and update only what we need to update, we need to apply a quick lookup. The condition I used is 

• If EmployeeID does not exist => insert
• If EmployeeID exists BUT name is different => update

Because there are plenty of resources about how to plan/design lookup procedures in SSIS, I am not going to cover this topic here (actually the screenshot is quite clear about the lookup procedure. But please bear in mind, SSIS is NULL sensitive.)

 

For most given Simpro APIs, we can try to follow codes above to pull the data from Simpro.

 

But as you can see from my comments in my codes above, I put the parameter limit as 65535. It is ok because from what I can see the system will not have more than 500 employees. But when we go to Quotes, Jobs, and Invoices, things could be a bit tricky: as far as I can see, I could not find some obvious fields to do simple lookup. And due to the Simpro design, I need to drill down to QuoteItem, JobItem, and InvoiceItem levels.

 

It was Ok at the beginning, but while the system is running, total time cost increased to a risk level. So in my in my next post, I will explain how I dealt with Quote, Job, and Invoice. :P