Simpro & SSIS Part 3: Loop the entity

This is the final post regarding Simpro and SSIS.

In previous posts, I had outlined how to access Simpro API via OAuth, and how to use SSIS Script component to extract Employee entity information through the Simpro API.

In this post, I want to discuss the actual performance issue I encountered when calling Simpro API.

In my project, what I need to do is to merge Simpro data with dialler’s data. So it is important to find some timestamps to indicate record creation and update datetime. However because of numeric reasons (cannot speak too much about it), I could not find an efficient way to get records I need, i.e. updated records and newly created records.

So what I did is like below

The important part is at the script component , where I wrote a loop to retrieve all quotes from Simpro, and then map the result to my existing Simpro_Quote table (the lookup component).

 public override void CreateNewOutputRows()  
   {     
     string consumerKey = "Your Consumer Key";  
     string consumerSecret = "Your Secret";  
     string url = @"http://URL/api/?format=json";  
     int Limit = 200;  
     int Offset = 0;  
     bool Continue = true;  
     while (Continue)  
     {  
       string JasonBuffer = GetJasonString(consumerKey, consumerSecret, url, Limit, Offset);  
       JObject jObject = JObject.Parse(JasonBuffer);  
       JArray Results = (JArray)jObject["result"];  
       List<int> lst = new List<int>();  
       foreach (JObject Result in Results)  
       {  
         JObject joCustomer = (JObject)Result["Customer"];  
         int QuoteID = 0;  
         bool Succeed = false;  
         Succeed = int.TryParse((string)Result["QuoteID"], out QuoteID);  
         Continue = QuoteID == 3000001 ? false : Continue;  
         foreach (JObject joSection in Result["Sections"])  
         {  
           int SectionID = 0;  
           Succeed = int.TryParse((string)joSection["SectionID"], out SectionID);  
           foreach (JObject joDepartment in joSection["Departments"])  
           {  
             int DepartmentID = 0;  
             int CostCentreID = 0;  
             Succeed = int.TryParse((string)joDepartment["DepartmentID"], out DepartmentID);  
             Succeed = int.TryParse((string)joDepartment["CostCentreID"], out CostCentreID);  
             foreach (JObject joItem in joDepartment["Items"])  
             {  
               int ItemID = 0;  
               Succeed = int.TryParse((string)joItem["ItemID"], out ItemID);  
               ItemBuffer.AddRow();  
               ItemBuffer.ItemID = ItemID;  
               ItemBuffer.DepartmentID = DepartmentID;  
               ItemBuffer.SectionID = SectionID;  
               ItemBuffer.QuoteID = QuoteID;  
               ItemBuffer.CostCentreID = CostCentreID;  
             }  
           }  
         }  
       }  
       Offset = Offset + Limit;  
     }  
   }  
   //base method to search quotes by loop  
   public string GetJasonString(string ConSumerKey, String ConsumerSecret, string Url, int Limit, int OffSet)  
   {  
     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>();  
     Params.Add("CompanyID", 0);  
     Params.Add("Limit", Limit);  
     Params.Add("Offset", OffSet);  
     Params.Add("SortAsc", false);  
     string Command = JSS.Serialize(new Dictionary<string, object>  
       {  
         {"id", "1"},  
         {"method", "QuoteSearch"},  
         {"params", Params}  
       }  
         );  
     byte[] bytes = Encoding.UTF8.GetBytes(Command);  
     byte[] bytesbuffer = Client.UploadData(Url, "POST", bytes);  
     JasonBuffer = Encoding.Default.GetString(bytesbuffer);  
     return JasonBuffer;  
   }  

It is not a good solution, but it works. If you ask me why I work in this way, probably what I can tell you are

• Simpro, like other online systems, can afford large amount of short and small requests simultaneously. So split the query into small pieces actually will fasten the data flow task.
• I tried to use parameters to limit number of records returned from Simpro, but it works when I need to find newly created records only. I could not find parameters to tell me which record was updated within a certain date/time range.
• Moreover, the Simpro APIs are not 100% correct. From my understanding these APIs are derived from  the interface reporting query. But for some reasons, some of the APIs will fail due to the query error.

So here is the last piece of the Simpro work. To me it is a good experience to work with something not perfect, but finally get problem resolved. Hopefully these posts can help you at some degree,