For this example, I'm going to use the Customer data type again. This has some tags in it that are used in a lot of other data types, i.e. address and date, so the classes defined for these data types can be re-used in other extracts that utilize those tags. All the data types and commands available to the API can be found here in their documentation.
To be able to pull data from the XML API, we need to send the API certain information in an XML block formulated as such:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><request API_version="1.0 " client="Client1" client_ver="1.1" namespace="default" key="providedkey"><Auth><Login><company>Company1</company><user>user1</user><password>password1</password></Login></Auth><Read type="Customer" filter="newer-than" field="updated" method="all" limit="0,1000" ><Date><year>2006</year><month>11</month><day>12</day></Date></Read><Time/></request>
This provides the API with authentication information and what kind of data we want to read. From this example we want to get Customer data, with a filter of newer-than 11/12/2006 on field updated. We want all records returned with an offset of 0 to 1000 records (the first 1000 records returned from the query). Like the SOAP API, this API only allows 1000 records to be read at a time. So quoting from my previous post "For example's sake, say we have 2050 customers in OpenAir. Our first request will have an offset and limit specified as such: 0,1000. This will return the first 1000 customers. The next request will be 1000,1000 this will return the next 1000 and finally 2000,1000, which will return to us the remaining 50 customers. So each time we make the call we move the offset window up 1000, since this is the maximum allowed." There are other filters available for reading and information/examples of those can be found in their documentation.
The response were expecting to see will be something like this(I only pulled back 1 customer for this screenshot):
 |
| Figure 1. XML API Response |
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<response>
<Auth status = "0"></Auth >
<Read status = "0">
<Customer>
<contactaddr>
<Address>
<salutation/>
<mobile/>
<state/>
<email/>
<addr2/>
<city/>
<fax/>
<addr1/>
<id>-1</id>
<middle/>
<country/>
<first/>
<last/>
<phone/>
<addr4/>
<zip/>
<addr3/>
</Address>
</contactaddr>
<invoice_layoutid/>
<bus_typeid/>
<rate>0.00</rate>
<updated>
<Date>
<hour>11</hour>
<minute>47</minute>
<timezone/>
<second>11</second>
<month>01</month>
<day>18</day>
<year>2012</year>
</Date>
</updated>
<id>1</id>
<filterset_ids/>
<code/>
<tb_approver/>
<sold_to_contactid/>
<billingaddr>
<Address>
<salutation/>
<mobile/>
<state/>
<email/>
<addr2/>
<city/>
<fax/>
<addr1/>
<id>-1</id>
<middle/>
<country/>
<first/>
<last/>
<phone/>
<addr4/>
<zip/>
<addr3/>
</Address>
</billingaddr>
<active>1</active>
<name>Customer1</name>
<territoryid/>
<billing_contact_id/>
<hierarchy_node_ids/>
<externalid/>
<billing_code/>
<hear_aboutid/>
<invoice_prefix/>
<type>C</type>
<statements>1</statements>
<userid>1</userid>
<terms/>
<createtime>
<Date>
<hour>17</hour>
<minute>35</minute>
<timezone/>
<second>13</second>
<month>08</month>
<day>01</day>
<year>2006</year>
</Date>
</createtime>
<company_sizeid/>
<ta_include>1</ta_include>
<currency/>
<web/>
<cost_centerid/>
<invoice_text/>
<company>Customer1</company>
<updatetime>
<Date>
<hour>11</hour>
<minute>47</minute>
<timezone/>
<second>11</second>
<month>01</month>
<day>18</day>
<year>2012</year>
</Date>
</updatetime>
<shipping_contactid/>
<created>
<Date>
<hour>17</hour>
<minute>35</minute>
<timezone/>
<second>13</second>
<month>08</month>
<day>01</day>
<year>2006</year>
</Date>
</created>
<te_include>1</te_include>
<notes/>
<tb_approvalprocess/>
<primary_contactid/>
<addr>
<Address>
<salutation/>
<mobile/>
<state/>
<email/>
<addr2/>
<city/>
<fax/>
<addr1/>
<id>-1</id>
<middle/>
<country/>
<first/>
<last/>
<phone/>
<addr4/>
<zip/>
<addr3/>
</Address>
</addr>
</Customer>
</Read >
<Time status = "0">
<Date>
<hour>16</hour>
<minute>6</minute>
<timezone>-0500</timezone>
<second>38</second>
<month>11</month>
<day>17</day>
<year>2013</year>
</Date>
</Time >
</response>
This will be the XML we need to parse out using SSIS. To begin this, we first need to create some variables that will be used to configure our authentication and read requests:
 |
| Figure 2. SSIS Variables |
All of the variables correspond with attributes/elements in the XML block we send to the XML API except for webService, timeout and data. The webService variable is the URL of the web service, timeout is the number of milliseconds the SSIS package should wait for a response from the web service before it times out and data takes all of the variables needed for XML block and formats them. Here is the value for this, make sure to evaluate this variable as an expression:
"<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><request API_version=\""+ @[User::apiVersion]+" \" client=\""+@[User::company]+"\" client_ver=\"1.1\" namespace=\"default\" key=\""+@[User::key]+"\"><Auth><Login><company>"+ @[User::company]+" </company><user>"+@[User::user]+"</user><password>"+@[User::password]+"</password></Login></Auth><Read type=\""+ @[User::type]+"\" filter=\""+@[User::filter]+"\" field=\""+@[User::field]+"\" method=\""+@[User::method]+"\" limit=\""+@[User::limit]+"\" ><Date><year>"+(DT_STR, 4, 1252) DATEPART("yyyy", @[User::dtBeginRange])+"</year><month>"+(DT_STR, 2, 1252) DATEPART("mm", @[User::dtBeginRange])+"</month><day>"+ (DT_STR, 2, 1252) DATEPART("dd", @[User::dtBeginRange])+"</day></Date></Read><Time/></request>"
 |
| Figure 3. Data Variable |
The data flow for this package is going to look something like this:
 |
| Figure 4. Data Flow |
To get the data from the XML API, we're going to make use of a Script Component source utilizing the variables we just created. So let's drag a script component from the tool box on to the data flow design surface. When prompted for type, select Source:
 |
| Figure 5. Script Component Type |
On the Script screen we need to make the variables we created accessible to the script component. We do this by adding them to ReadOnlyVariables:
 |
| Figure 6. Script Component Script Screen |
User::apiVersion,User::client,User::company,User::data,User::dtBeginRange,User::field,User::filter,User::key,User::limit,User::method,User::password,User::timeout,User::type,User::user,User::webService
We then have to configure our output buffer, so click on Inputs and Outputs to pull this up. On this screen we define what the fields for Customer are going to look like. A good reference for data types can be found here. I defaulted all the fields to DT_STR data types, since they all come back as strings anyways, except for Notes which I made DT_TEXT. But ultimately the data types are up to you, just keep in mind you will have to convert them from strings in code before sending the values to the output buffer:
 |
| Figure 7. Script Component Input and Output Screen |
Now that we're done entering all of our Customer fields, we can start coding. Go back to the Script screen, click on the Edit Script button and let's start coding:
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2012.
* ScriptMain is the entry point class of the script.*/
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Text;
using System.Xml.Serialization;
#endregion
#region Class
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
//Stores the current offset value
int offSet = 0;
//Holds customer objects returned
OpenAirResponse outputCustomers;
#region Methods
/// <summary>Outputs records to the output buffer</summary>
public override void CreateNewOutputRows()
{
//Data flow interface
IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
try
{
do
{
//Calls web service
outputCustomers = GetWebServiceResult(Variables.webService, Variables.data, offSet, compMetadata);
//Checks For Authentication Errors
if (outputCustomers.auth.status > 0)
{
FailComponent(String.Format("Authentication Error: {0}:{1} [{2}]",outputCustomers.auth.status, GetAuthErrorMessage(outputCustomers.auth.status),DateTime.Now),compMetadata);
Output0Buffer.SetEndOfRowset();
break;
}
//Checks For Read Errors
if (outputCustomers.read.status > 0)
{
FailComponent(String.Format("Read Error: {0}:{1} [{2}]",outputCustomers.read.status,outputCustomers.read.readMsg, DateTime.Now), compMetadata);
Output0Buffer.SetEndOfRowset();
break;
}
//If we have no customers in the array, set the end of row set
if (outputCustomers.read.customerArray == null)
{
Output0Buffer.SetEndOfRowset();
throw new NullReferenceException();
}
else
{
InfoComponent(String.Format("Adding results {0}, {1} to the output buffer [{2}]", offSet, Variables.limit, DateTime.Now), compMetadata);
foreach (Customer cust in outputCustomers.read.customerArray)
{
Output0Buffer.AddRow();
Output0Buffer.ID = cust.id;
Output0Buffer.ADDRSALUTATION = cust.addr.address.salutation;
Output0Buffer.ADDRMOBILE = cust.addr.address.mobile;
Output0Buffer.ADDRSTATE = cust.addr.address.state;
Output0Buffer.ADDREMAIL = cust.addr.address.email;
Output0Buffer.ADDRADDR2 = cust.addr.address.addr2;
Output0Buffer.ADDRCITY = cust.addr.address.city;
Output0Buffer.ADDRFAX = cust.addr.address.fax;
Output0Buffer.ADDRADDR1 = cust.addr.address.addr1;
Output0Buffer.ADDRMIDDLE = cust.addr.address.middle;
Output0Buffer.ADDRCOUNTRY = cust.addr.address.country;
Output0Buffer.ADDRFIRST = cust.addr.address.first;
Output0Buffer.ADDRLAST = cust.addr.address.last;
Output0Buffer.ADDRPHONE = cust.addr.address.phone;
Output0Buffer.ADDRADDR4 = cust.addr.address.addr4;
Output0Buffer.ADDRZIP = cust.addr.address.zip;
Output0Buffer.ADDRADDR3 = cust.addr.address.addr3;
Output0Buffer.INVOICELAYOUTID = cust.invoice_layoutid;
Output0Buffer.RATE = cust.rate;
Output0Buffer.BUSTYPEID = cust.bus_typeid;
Output0Buffer.CODE = cust.code;
Output0Buffer.NAME = cust.name;
Output0Buffer.TBAPPROVER = cust.tb_approver;
Output0Buffer.TERRITORYID = cust.territoryid;
Output0Buffer.HIERARCHYNODEIDS = cust.hierarchy_node_ids;
Output0Buffer.HEARABOUTID = cust.hear_aboutid;
Output0Buffer.STATEMENTS = cust.statements;
Output0Buffer.COMPANYSIZEID = cust.company_sizeid;
Output0Buffer.WEB = cust.web;
Output0Buffer.CURRENCY = cust.currency;
Output0Buffer.COSTCENTERID = cust.cost_centerid;
Output0Buffer.CONTACTADDRSALUTATION = cust.contactaddr.address.salutation;
Output0Buffer.CONTACTADDRMOBILE = cust.contactaddr.address.mobile;
Output0Buffer.CONTACTADDRSTATE = cust.contactaddr.address.state;
Output0Buffer.CONTACTADDREMAIL = cust.contactaddr.address.email;
Output0Buffer.CONTACTADDRADDR2 = cust.contactaddr.address.addr2;
Output0Buffer.CONTACTADDRCITY = cust.contactaddr.address.city;
Output0Buffer.CONTACTADDRFAX = cust.contactaddr.address.fax;
Output0Buffer.CONTACTADDRADDR1 = cust.contactaddr.address.addr1;
Output0Buffer.CONTACTADDRMIDDLE = cust.contactaddr.address.middle;
Output0Buffer.CONTACTADDRCOUNTRY = cust.contactaddr.address.country;
Output0Buffer.CONTACTADDRFIRST = cust.contactaddr.address.first;
Output0Buffer.CONTACTADDRLAST = cust.contactaddr.address.last;
Output0Buffer.CONTACTADDRPHONE = cust.contactaddr.address.phone;
Output0Buffer.CONTACTADDRADDR4 = cust.contactaddr.address.addr4;
Output0Buffer.CONTACTADDRZIP = cust.contactaddr.address.zip;
Output0Buffer.CONTACTADDRADDR3 = cust.contactaddr.address.addr3;
Output0Buffer.BILLINGADDRSALUTATION = cust.billingaddr.address.salutation;
Output0Buffer.BILLINGADDRMOBILE = cust.billingaddr.address.mobile;
Output0Buffer.BILLINGADDRSTATE = cust.billingaddr.address.state;
Output0Buffer.BILLINGADDREMAIL = cust.billingaddr.address.email;
Output0Buffer.BILLINGADDRADDR2 = cust.billingaddr.address.addr2;
Output0Buffer.BILLINGADDRCITY = cust.billingaddr.address.city;
Output0Buffer.BILLINGADDRFAX = cust.billingaddr.address.fax;
Output0Buffer.BILLINGADDRADDR1 = cust.billingaddr.address.addr1;
Output0Buffer.BILLINGADDRMIDDLE = cust.billingaddr.address.middle;
Output0Buffer.BILLINGADDRCOUNTRY = cust.billingaddr.address.country;
Output0Buffer.BILLINGADDRFIRST = cust.billingaddr.address.first;
Output0Buffer.BILLINGADDRLAST = cust.billingaddr.address.last;
Output0Buffer.BILLINGADDRPHONE = cust.billingaddr.address.phone;
Output0Buffer.BILLINGADDRADDR4 = cust.billingaddr.address.addr4;
Output0Buffer.BILLINGADDRZIP = cust.billingaddr.address.zip;
Output0Buffer.BILLINGADDRADDR3 = cust.billingaddr.address.addr3;
Output0Buffer.BILLINGCONTACTID = cust.billing_contact_id;
Output0Buffer.NOTES.AddBlobData(Encoding.ASCII.GetBytes(cust.notes));
Output0Buffer.TBAPPROVALPROCESS = cust.tb_approvalprocess;
Output0Buffer.PRIMARYCONTACTID = cust.primary_contactid;
Output0Buffer.FILTERSETIDS = cust.filterset_ids;
Output0Buffer.ACTIVE = cust.active;
Output0Buffer.EXTERNALID = cust.externalid;
Output0Buffer.INVOICEPREFIX = cust.invoice_prefix;
Output0Buffer.TYPE = cust.type;
Output0Buffer.USERID = cust.userid;
Output0Buffer.TERMS = cust.terms;
Output0Buffer.CREATED = ParseDates(cust.created.date);
Output0Buffer.INVOICETEXT = cust.invoice_text;
Output0Buffer.COMPANY = cust.company;
Output0Buffer.UPDATED = ParseDates(cust.updated.date);
Output0Buffer.SHIPPINGCONTACTID = cust.shipping_contactid;
Output0Buffer.SOLDTOCONTACTID = cust.sold_to_contactid;
Output0Buffer.BILLINGCODE = cust.billing_code;
Output0Buffer.CREATETIME = ParseDates(cust.createtime.date);
Output0Buffer.TAINCLUDE = cust.ta_include;
Output0Buffer.TEINCLUDE = cust.te_include;
Output0Buffer.UPDATETIME = ParseDates(cust.updatetime.date);
}
offSet += Convert.ToInt32(Variables.limit);
}
} while (outputCustomers.read.customerArray != null && outputCustomers.read.customerArray.Length == Convert.ToInt32(Variables.limit));
}
catch (NullReferenceException)
{
if (offSet == 0)
{
InfoComponent(String.Format("No records were returned from web service that were newer than {0} [{1}]",Variables.dtBeginRange.ToString("MM/dd/yyyy"), DateTime.Now),compMetadata);
}
}
catch (Exception e)
{
FailComponent(e.ToString(), compMetadata);
}
Finished(compMetadata);
}
/// <summary>
/// Outputs a completed processing message
/// </summary>
/// <param name="compMetadata">The dataflow interface</param>
private void Finished(IDTSComponentMetaData100 compMetadata)
{
InfoComponent(String.Format("Retrieving records completed [{0}]", DateTime.Now), compMetadata);
}
/// <summary>Pulls down XML from the Open Air web service and returns a deserialized array of objects</summary>
/// <param name="wUrl">The webserver URL to call</param>
/// <param name="wData">The data to send to the web service</param>
/// <returns>Returns a Task array deserialzed from the web service </returns>
private OpenAirResponse GetWebServiceResult(string wUrl, string wData, int offSet, IDTSComponentMetaData100 compMetadata)
{
//Request
HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
//Holds customers
OpenAirResponse xmlResponse = null;
//Set data were going to send to the web service
byte[] data = new ASCIIEncoding().GetBytes(wData.Replace("limit=\"", "limit=\"" + offSet.ToString() + ","));
httpWReq.Method = "Post";
httpWReq.Timeout = Variables.timeout;
httpWReq.ContentLength = data.Length;
InfoComponent(String.Format("Sending data to web service [{0}]", DateTime.Now), compMetadata);
//Send data to the web service
using (Stream myStream = httpWReq.GetRequestStream())
{
myStream.Write(data, 0, data.Length);
}
InfoComponent(String.Format("Waiting for response from web service [{0}]", DateTime.Now), compMetadata);
//Response
HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
try
{
//Test the connection
if (httpWResp.StatusCode == HttpStatusCode.OK)
{
InfoComponent(String.Format("Retrieving results {0}, {1} from web service [{2}]", offSet, Variables.limit, DateTime.Now), compMetadata);
Stream responseStream = httpWResp.GetResponseStream();
InfoComponent(String.Format("Deserializing XML [{0}]", DateTime.Now), compMetadata);
//Set xmlString using a stream reader
using (StreamReader reader = new StreamReader(responseStream))
{
XmlSerializer sr = new XmlSerializer(typeof(OpenAirResponse));
xmlResponse = (OpenAirResponse)sr.Deserialize(reader);
}
}
//Output connection error message
else
{
FailComponent(String.Format("Http Error! {0}:[{1}]", httpWResp.StatusCode.ToString(), DateTime.Now), compMetadata);
}
}
//Output xml parsing error
catch (Exception e)
{
FailComponent(String.Format("Error! {0}:[{1}]", e.ToString(), DateTime.Now), compMetadata);
}
return xmlResponse;
}
/// <summary>Formats a date string</summary>
/// <param name="date">The date object being parsed</param>
/// <returns>Returns a formatted string based on the values in the string</returns>
private string ParseDates(Date date)
{
string returnDate = "";
if (date.month != "" && date.month != "00")
{
returnDate = date.month + "/" + date.day + "/" + date.year + " ";
}
if (date.hour != "")
{
returnDate += date.hour + ":" + date.minute + ":" + date.second;
}
if (date.timezone != "")
{
returnDate += " " + date.timezone;
}
return returnDate;
}
/// <summary>
/// Returns a summary of an authentication error from the web service, since they are not provided
/// </summary>
/// <param name="status">The authentication status code</param>
/// <returns>The message associated with the status code</returns>
private string GetAuthErrorMessage(int status)
{
string msg = "";
switch (status)
{
case 401:
msg = "Auth failed : No such company/user/pass";
break;
case 402:
msg = "Old TB login";
break;
case 403:
msg = "No Company name supplied";
break;
case 404:
msg = "No User name supplied";
break;
case 405:
msg = "No User Password supplied";
break;
case 406:
msg = "Invalid Company name";
break;
case 408:
msg = "Bad Password";
break;
case 409:
msg = "Account Canceled";
break;
case 410:
msg = "Inactive user";
break;
case 411:
msg = "Account conflict, contact customer service";
break;
case 412:
msg = "Wrong namespace for account";
break;
case 413:
msg = "Account not privileged to access API";
break;
case 414:
msg = "Temporarily unavailable";
break;
case 415:
msg = "Account archived";
break;
case 416:
msg = "User Locked";
break;
case 417:
msg = "Restricted IP address";
break;
case 418:
msg = "Invalid uid session";
break;
case 422:
msg = "LDAP server unavailable";
break;
}
return msg;
}
/// <summary>Outputs an Error</summary>
/// <param name="errorMsg">The error message to send to the UI</param>
private void FailComponent(string errorMsg, IDTSComponentMetaData100 compMetadata)
{
bool fail = false;
compMetadata.FireError(1, compMetadata.Name, errorMsg, "", 0, out fail);
}
/// <summary>Outputs a message if no data is returned /// </summary>
/// <param name="errorMsg">The no data returned message</param>
private void InfoComponent(string errorMsg, IDTSComponentMetaData100 compMetadata)
{
bool fail = false;
compMetadata.FireInformation(1, compMetadata.Name, errorMsg, "", 0, ref fail);
}
#endregion
}
#endregion
#region XML Classes
//Class to hold XML Root Element
[XmlRootAttribute("response")]//<response>
public class OpenAirResponse
{
[XmlElement("Auth")] //<Auth>
public OpenAirAuth auth;
[XmlElement("Read")] //<Read>
public OpenAirRead read;
}
//Class to hold XML Element Auth
public class OpenAirAuth
{
[XmlAttribute(AttributeName = "status")] //<Auth status= >
public int status;
[XmlTextAttribute]//<Auth>Authentication Message</Auth>
public string authMsg;
}
//Class to hold XML Element Read
public class OpenAirRead
{
[XmlAttribute(AttributeName = "status")]//<Read status= >
public int status;
[XmlTextAttribute]//<Read>Read Message</Read>
public string readMsg;
[XmlElement("Customer")]//<Customer>
public Customer[] customerArray { get; set; }
}
public class OpenAirAddress
{
[XmlElement("Address")]//<Address>
public Address address { get; set; }
}
public class OpenAirDate
{
[XmlElement("Date")]//<Date>
public Date date { get; set; }
}
//Class to hold date tags
public class Date
{
[XmlElement("hour")]//<Date><hour/><Date>
public string hour { get; set; }
[XmlElement("minute")]//<Date><minute/><Date>
public string minute { get; set; }
[XmlElement("timezone")]//<Date><timezone/><Date>
public string timezone { get; set; }
[XmlElement("second")]//<Date><second/><Date>
public string second { get; set; }
[XmlElement("month")]//<Date><month/><Date>
public string month { get; set; }
[XmlElement("day")]//<Date><day/><Date>
public string day { get; set; }
[XmlElement("year")]//<Date><year/><Date>
public string year { get; set; }
}
//Class to hold address tags
public class Address
{
[XmlElement("salutation")]//<Address><salutation/></Address>
public string salutation { get; set; }
[XmlElement("mobile")]//<Address><mobile/></Address>
public string mobile { get; set; }
[XmlElement("state")]//<Address><state/></Address>
public string state { get; set; }
[XmlElement("email")]//<Address><email/></Address>
public string email { get; set; }
[XmlElement("addr2")]//<Address><addr2/></Address>
public string addr2 { get; set; }
[XmlElement("city")]//<Address><city/></Address>
public string city { get; set; }
[XmlElement("fax")]//<Address><fax/></Address>
public string fax { get; set; }
[XmlElement("addr1")]//<Address><addr1/></Address>
public string addr1 { get; set; }
[XmlElement("middle")]//<Address><middle/></Address>
public string middle { get; set; }
[XmlElement("country")]//<Address><country/></Address>
public string country { get; set; }
[XmlElement("first")]//<Address><first/></Address>
public string first { get; set; }
[XmlElement("last")]//<Address><last/></Address>
public string last { get; set; }
[XmlElement("phone")]//<Address><phone/></Address>
public string phone { get; set; }
[XmlElement("addr4")]//<Address><addr4/></Address>
public string addr4 { get; set; }
[XmlElement("zip")]//<Address><zip/></Address>
public string zip { get; set; }
[XmlElement("addr3")]//<Address><addr3/></Address>
public string addr3 { get; set; }
}
//Class to hold XML Elements
public class Customer
{
[XmlElement("id")]//<Customer><id/></Customer>
public string id { get; set; }
[XmlElement("addr")]//<Customer><addr/></Customer>
public OpenAirAddress addr { get; set; }
[XmlElement("invoice_layoutid")]//<Customer><invoice_layoutid/></Customer>
public string invoice_layoutid { get; set; }
[XmlElement("rate")]//<Customer><rate/></Customer>
public string rate { get; set; }
[XmlElement("bus_typeid")]//<Customer><bus_typeid/></Customer>
public string bus_typeid { get; set; }
[XmlElement("code")]//<Customer><code/></Customer>
public string code { get; set; }
[XmlElement("name")]//<Customer><name/></Customer>
public string name { get; set; }
[XmlElement("tb_approver")]//<Customer><tb_approver/></Customer>
public string tb_approver { get; set; }
[XmlElement("territoryid")]//<Customer><territoryid/></Customer>
public string territoryid { get; set; }
[XmlElement("hierarchy_node_ids")]//<Customer><hierarchy_node_ids/></Customer>
public string hierarchy_node_ids { get; set; }
[XmlElement("hear_aboutid")]//<Customer><hear_aboutid/></Customer>
public string hear_aboutid { get; set; }
[XmlElement("statements")]//<Customer><statements/></Customer>
public string statements { get; set; }
[XmlElement("company_sizeid")]//<Customer><company_sizeid/></Customer>
public string company_sizeid { get; set; }
[XmlElement("web")]//<Customer><web/></Customer>
public string web { get; set; }
[XmlElement("currency")]//<Customer><currency/></Customer>
public string currency { get; set; }
[XmlElement("cost_centerid")]//<Customer><cost_centerid/></Customer>
public string cost_centerid { get; set; }
[XmlElement("contactaddr")]//<Customer><contactaddr/></Customer>
public OpenAirAddress contactaddr { get; set; }
[XmlElement("billingaddr")]//<Customer><billingaddr/></Customer>
public OpenAirAddress billingaddr { get; set; }
[XmlElement("billing_contact_id")]//<Customer><billing_contact_id/></Customer>
public string billing_contact_id { get; set; }
[XmlElement("notes")]//<Customer><notes/></Customer>
public string notes { get; set; }
[XmlElement("tb_approvalprocess")]//<Customer><tb_approvalprocess/></Customer>
public string tb_approvalprocess { get; set; }
[XmlElement("primary_contactid")]//<Customer><primary_contactid/></Customer>
public string primary_contactid { get; set; }
[XmlElement("filterset_ids")]//<Customer><filterset_ids/></Customer>
public string filterset_ids { get; set; }
[XmlElement("active")]//<Customer><active/></Customer>
public string active { get; set; }
[XmlElement("externalid")]//<Customer><externalid/></Customer>
public string externalid { get; set; }
[XmlElement("invoice_prefix")]//<Customer><invoice_prefix/></Customer>
public string invoice_prefix { get; set; }
[XmlElement("type")]//<Customer><type/></Customer>
public string type { get; set; }
[XmlElement("userid")]//<Customer><userid/></Customer>
public string userid { get; set; }
[XmlElement("terms")]//<Customer><terms/></Customer>
public string terms { get; set; }
[XmlElement("created")]//<Customer><created/></Customer>
public OpenAirDate created { get; set; }
[XmlElement("invoice_text")]//<Customer><invoice_text/></Customer>
public string invoice_text { get; set; }
[XmlElement("company")]//<Customer><company/></Customer>
public string company { get; set; }
[XmlElement("updated")]//<Customer><updated/></Customer>
public OpenAirDate updated { get; set; }
[XmlElement("shipping_contactid")]//<Customer><shipping_contactid/></Customer>
public string shipping_contactid { get; set; }
[XmlElement("sold_to_contactid")]//<Customer><shipping_contactid/></Customer>
public string sold_to_contactid { get; set; }
[XmlElement("billing_code")]//<Customer><billing_code/></Customer>
public string billing_code { get; set; }
[XmlElement("createtime")]//<Customer><createtime/></Customer>
public OpenAirDate createtime { get; set; }
[XmlElement("ta_include")]//<Customer><ta_include/></Customer>
public string ta_include { get; set; }
[XmlElement("te_include")]//<Customer><te_include/></Customer>
public string te_include { get; set; }
[XmlElement("updatetime")]//<Customer><updatetime/></Customer>
public OpenAirDate updatetime { get; set; }
}
#endregion
Now let's step through and explain some of this code. The first thing we want to do is override the CreateNewOutputRows method public override void CreateNewOutputRows() to create our new rows from the web service. We then set our OpenAirResponse object to the result of the de-serialized XML outputCustomers = GetWebServiceResult(Variables.webService, Variables.data, offSet, compMetadata). Each response we get back from the web service will be de-serialized into an OpenAirResponse object. This class matches the format of the XML being returned from the web service. We then loop through the customerArray in the OpenAirResponse object and output any authentication errors, read errors, and most importantly our customer data
foreach (Customer cust in outputCustomers.read.customerArray)
{
Output0Buffer.AddRow();
Output0Buffer.ID = cust.id;
....
While looping, we iterate the offset specified in the limit attribute by 1000 (limit specified in our variable) each time until we have returned all the customers from the query:
byte[] data = new ASCIIEncoding().GetBytes(wData.Replace("limit=\"", "limit=\"" + offSet.ToString() + ","));
Now let save, build and run our package to view the results in our data viewer:
 |
| Figure 8. Data Viewer |
While the package is running, we can keep an eye on the status from the progress tab:
 |
| Figure 9. Progress Tab |
For read errors, we output the error code and description returned from the web service FailComponent(String.Format("Read Error: {0}:{1} [{2}]",outputCustomers.read.status,outputCustomers.read.readMsg, DateTime.Now), compMetadata):
 |
| Figure 10. Read Error |
We can also output any authentication errors. The web service appears to return the code but not the description. So I pulled the descriptions from the API documentation and created a method to return them private string GetAuthErrorMessage(int status).
FailComponent(String.Format("Authentication Error: {0}:{1} [{2}]",outputCustomers.auth.status, GetAuthErrorMessage(outputCustomers.auth.status),DateTime.Now),compMetadata);
If they ever start adding these messages, you can simply change the code to this to return from the web service instead of GetAuthErrorMessage:
FailComponent(String.Format("Authentication Error: {0}:{1} [{2}]",outputCustomers.auth.status, outputCustomers.auth.authMsg,DateTime.Now),compMetadata);
 |
| Figure 11. Authentication Error |
If our query returns no results, that will be displayed as well:
 |
| Figure 12. No Records Returned Message |
Get the Best Bulk Product Upload Services From Us With the drastic facelift that the retail sector has seen in the recent past, the internet gas become the hub for most of the small, medium as well as large scale businesses that are involved in the same.The ecommerce has seen a monumental growth prospective over the last couple of years and the trend is expected to remain the same for the foreseeable future as well. In this regard, what concerns the businesses and the online retail outlet is the data entry aspect. Tech data entry India provides a number of products and services to cater to the Amazon Bulk Product Listing Services.
ReplyDelete