When going through their API documentation there are a number of commands available you can call to view and manipulate data. The only one we are concerned with here is the "Read" command, since all we want to do is view the data. Now we need to decide what piece of data we want to look at. There are a number of data types you can choose from, for this example we want to retrieve a list of Customer attributes. So we need to specify "Customer" as our type.
After choosing a data type, we need to further specify what data we want by selecting a "Method". These can select all records returned, records that equal a certain value/condition, not equal to a certain value/condition, etc. For this example, we are just going to pull back all the records. In order to limit these records we want to set a filter. These can be business oriented like approved-timesheets or rejected-timesheets or date range based such as newer-than, older-than, date-equal-to or date-not-equal-to. For this example I'm going to use a date filter and use newer-than. This will allow me to get records that have been updated since a certain date. We can do this by applying this filter to a "Field". Every record that is returned from the web service has an updated field, so we can choose this as our filter field. The date we want to specify can be added to a date tag.
Finally, we need to set up our limit. Limit tells the API how many records to return at a time, with a maximum of 1000 records for any request. This makes things a little complicated. However; we can specify an offset for this which will allow us to get all the records we need. 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.
The client, company key, user and password will all be specific to the user and will be provided by NetSuite. You can test this out in your browser using their XML API, if you're using Chrome, using the Simple REST Client addon:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><request API_version="1.0 " client="Company1" 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>
 |
| Figure 1. OpenAir XML API |
Now that we are sure the data we are sending to the web service is well formatted, we can start building our package that will consume the feed. Our package is going to have a single data flow that is going to look like this:
 |
| Figure 2. Data Flow |
Ignore the Union All component, that's just there so I can add a data viewer, normally this would go to a data destination.
First, we need to take all of the tags were passing to the web service and make variables for them. This will allow us to change configurations when necessary, without having to touch any of the underlying C# code:
 |
| Figure 3. Variables |
You'll notice that all of the variables correspond to values used in the XML block. The only one that doesn't is timeout. This is the time in milliseconds the package will wait for a response from the web service before timing out, I defaulted it to 1 minute.
Now that we have created our variables, we need to create our Script Component source that will call the web service and get us our data. Drag a script component onto the design surface. When prompted, select source:
 |
| Figure 4. Script Component Type |
On the script screen, make sure to pick variables: User::apiVersion,User::client,User::company,User::dtBeginRange,User::field,User::filter,User::key,User::limit,User::method,User::password,User::timeout,User::type,User::user as ReadOnlyVariables:
 |
| Figure 5. Script Component Script Screen |
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 6. Script Component Inputs and Outputs Screen |
Now that we're done entering all of our Customer fields, we can start coding. Go back to the Script screen and click on the Edit Script button. Before we can start coding we need to import OpenAir's WSDL to our project, there is a page here provided by Microsoft on how to do this but I'll walk through it anyways. In the script component environment click on Project -->Add Service Reference:
 |
| Figure 7. Project Menu |
This will bring up the Add Service Reference window, click on the Advanced button:
 |
| Figure 8. Add Service Reference Window |
This will bring up the Service Reference Settings window, click on the Add Web Reference button:
 |
| Figure 9. Service Reference Settings Window |
This will bring up the Add Web Reference window where we can (finally) specify our reference. in the URL text box type http://www.openair.com/wsdl.pl?wsdl then click the arrow button to the right of the text box. This will activate the Web Reference name text box. Type "OA" in this box, its what is preferred according to the API documentation, then click the Add Reference button:
 |
| Figure 10. Add Web Reference Window |
You should now see this added to your project under Web References:
 |
| Figure 11. Project Files |
Now we can use this in our code in main.cs:
We can also output authentication errors from the web service:
As well as read errors:
To output read errors, we call FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, GetReadErrorMessage( Convert.ToInt32(err.code)), DateTime.Now), compMetadata);. It appears that err.text and err.comment are always blank. So I pulled the text of the errors from the API document. If these values ever start appearing in future versions of the API you can replace the code with this:
FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, err.text, DateTime.Now), compMetadata);
If our query returns no results, we display that as well:
/* 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 Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using System.Text;
using SC_e49c934c98564fa6be517b883cd15def.OA;
using OA = SC_e49c934c98564fa6be517b883cd15def.OA;
using System.Web.Services.Protocols;
#endregion
#region Class
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Methods
/// <summary>Outputs records to the output buffer</summary>
public override void CreateNewOutputRows()
{
IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
//DataFlow component interface
//Stores the current offset value
int offSet = 0;
//Stores the results returned from the web service
ReadResult[] results;
//Auth error flag
bool authError = false;
//Service Session Handler
OAirServiceHandlerService _svc=null;
try
{
//Creates a read request object to send to the web service
ReadRequest rr = GetReadRequest();
//Login and get session
_svc = GetServiceHandler(compMetadata, ref authError);
//If we had an authentication error, we end
if (authError == false)
{
do
{
//Iterate offSet in read request
rr.attributes[2].value = String.Format("{0}, {1} [{2}]", offSet, Variables.limit, DateTime.Now);
InfoComponent(String.Format("Loading results {0}, {1} [{2}]", offSet, Variables.limit, DateTime.Now), compMetadata);
results = _svc.read(new ReadRequest[] { rr });
//If we have an error reading records, output errors
if (results != null && results.Length > 0 && results[0].errors != null)
{
foreach (oaError err in results[0].errors)
{
FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, GetReadErrorMessage(Convert.ToInt32(err.code)), DateTime.Now), compMetadata);
}
break;
}
//If we have no records returned
if (results == null)
{
Output0Buffer.SetEndOfRowset();
Logout(_svc, compMetadata);
throw new NullReferenceException();
}
else
{
InfoComponent(String.Format("Adding results {0}, {1} to the output buffer [{2}]", offSet, Variables.limit, DateTime.Now), compMetadata);
foreach (oaCustomer cust in results[0].objects)
{
Output0Buffer.AddRow();
Output0Buffer.ID = cust.id;
Output0Buffer.ADDRSALUTATION = cust.addr_salutation;
Output0Buffer.ADDRMOBILE = cust.addr_mobile;
Output0Buffer.ADDRSTATE = cust.addr_state;
Output0Buffer.ADDREMAIL = cust.addr_email;
Output0Buffer.ADDRADDR2 = cust.addr_addr2;
Output0Buffer.ADDRCITY = cust.addr_city;
Output0Buffer.ADDRFAX = cust.addr_fax;
Output0Buffer.ADDRADDR1 = cust.addr_addr1;
Output0Buffer.ADDRMIDDLE = cust.addr_middle;
Output0Buffer.ADDRCOUNTRY = cust.addr_country;
Output0Buffer.ADDRFIRST = cust.addr_first;
Output0Buffer.ADDRLAST = cust.addr_last;
Output0Buffer.ADDRPHONE = cust.addr_phone;
Output0Buffer.ADDRADDR4 = cust.addr_addr4;
Output0Buffer.ADDRZIP = cust.addr_zip;
Output0Buffer.ADDRADDR3 = cust.addr_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.contact_addr_salutation;
Output0Buffer.CONTACTADDRMOBILE = cust.contact_addr_mobile;
Output0Buffer.CONTACTADDRSTATE = cust.contact_addr_state;
Output0Buffer.CONTACTADDREMAIL = cust.contact_addr_email;
Output0Buffer.CONTACTADDRADDR2 = cust.contact_addr_addr2;
Output0Buffer.CONTACTADDRCITY = cust.contact_addr_city;
Output0Buffer.CONTACTADDRFAX = cust.contact_addr_fax;
Output0Buffer.CONTACTADDRADDR1 = cust.contact_addr_addr1;
Output0Buffer.CONTACTADDRMIDDLE = cust.contact_addr_middle;
Output0Buffer.CONTACTADDRCOUNTRY = cust.contact_addr_country;
Output0Buffer.CONTACTADDRFIRST = cust.contact_addr_first;
Output0Buffer.CONTACTADDRLAST = cust.contact_addr_last;
Output0Buffer.CONTACTADDRPHONE = cust.contact_addr_phone;
Output0Buffer.CONTACTADDRADDR4 = cust.contact_addr_addr4;
Output0Buffer.CONTACTADDRZIP = cust.contact_addr_zip;
Output0Buffer.CONTACTADDRADDR3 = cust.contact_addr_addr3;
Output0Buffer.BILLINGADDRSALUTATION = cust.billing_addr_salutation;
Output0Buffer.BILLINGADDRMOBILE = cust.billing_addr_mobile;
Output0Buffer.BILLINGADDRSTATE = cust.billing_addr_state;
Output0Buffer.BILLINGADDREMAIL = cust.billing_addr_email;
Output0Buffer.BILLINGADDRADDR2 = cust.billing_addr_addr2;
Output0Buffer.BILLINGADDRCITY = cust.billing_addr_city;
Output0Buffer.BILLINGADDRFAX = cust.billing_addr_fax;
Output0Buffer.BILLINGADDRADDR1 = cust.billing_addr_addr1;
Output0Buffer.BILLINGADDRMIDDLE = cust.billing_addr_middle;
Output0Buffer.BILLINGADDRCOUNTRY = cust.billing_addr_country;
Output0Buffer.BILLINGADDRFIRST = cust.billing_addr_first;
Output0Buffer.BILLINGADDRLAST = cust.billing_addr_last;
Output0Buffer.BILLINGADDRPHONE = cust.billing_addr_phone;
Output0Buffer.BILLINGADDRADDR4 = cust.billing_addr_addr4;
Output0Buffer.BILLINGADDRZIP = cust.billing_addr_zip;
Output0Buffer.BILLINGADDRADDR3 = cust.billing_addr_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 = cust.created;
Output0Buffer.INVOICETEXT = cust.invoice_text;
Output0Buffer.COMPANY = cust.company;
Output0Buffer.UPDATED = cust.updated;
Output0Buffer.SHIPPINGCONTACTID = cust.shipping_contactid;
Output0Buffer.SOLDTOCONTACTID = cust.sold_to_contactid;
Output0Buffer.BILLINGCODE = cust.billing_code;
Output0Buffer.CREATETIME = cust.createtime;
Output0Buffer.TAINCLUDE = cust.ta_include;
Output0Buffer.TEINCLUDE = cust.te_include;
Output0Buffer.UPDATETIME = cust.updatetime;
}
}
//Fetch next 1000 records
offSet += Convert.ToInt32(Variables.limit);
//Keep loop going while we have records and the records returned are = Variables.limit
} while (results[0].objects != null && results[0].objects.Length == Convert.ToInt32(Variables.limit));
}
else
{
Output0Buffer.EndOfRowset();
}
}
catch (NullReferenceException)
{
if (offSet == 0)
{
InfoComponent(String.Format("No records were returned from webservice that were newer than {0} [{1}]", Variables.dtBeginRange.ToString("MM/dd/yyyy"),DateTime.Now), compMetadata);
}
}
catch (SoapException se)
{
Output0Buffer.EndOfRowset();
authError = true;
FailComponent(se.ToString(), compMetadata);
}
catch (Exception e)
{
Output0Buffer.EndOfRowset();
FailComponent(e.ToString(), compMetadata);
}
//Log out of web service
Finished(compMetadata);
Logout(_svc, compMetadata);
}
/// <summary>
/// Returns a summary of an read error from the web service, since they are not provided
/// </summary>
/// <param name="status">The read status code</param>
/// <returns>The message associated with the status code</returns>
private string GetReadErrorMessage(int status)
{
string msg = "";
switch (status)
{
case 601:
msg = "Invalid id/code";
break;
case 602:
msg = "Invalid field";
break;
case 603:
msg = "Invalid type or method";
break;
case 604:
msg = "Attachment size exceeds space available";
break;
case 605:
msg = "Limit clause must be specified and be less than the account limit for output data";
break;
}
return msg;
}
/// <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>
/// Logs out of OpenAir
/// </summary>
/// <param name="svc">The OAirServiceHandlerService object that contains our session </param>
/// <param name="compMetadata">The dataflow interface</param>
private void Logout(OAirServiceHandlerService svc, IDTSComponentMetaData100 compMetadata)
{
InfoComponent(String.Format("Logging out of Open Air [{0}]", DateTime.Now), compMetadata);
if(svc!=null)
{
svc.logout();
}
}
/// <summary>
/// Returns the read request that were going to send to the web service, our parameters from variables are assigned here
/// </summary>
/// <returns>Returns the read request</returns>
public ReadRequest GetReadRequest()
{
ReadRequest rr = new ReadRequest();
//Set the datatype we want to return
rr.type = Variables.type;
//return only records that match search criteria
rr.method = Variables.method;
//Filter by date range and by the special not-exported flag
OA.Attribute attrFilter = new OA.Attribute();
attrFilter.name = "filter";
attrFilter.value = Variables.filter;
//Name of the field to apply date filter to
OA.Attribute attrField = new OA.Attribute();
attrField.name = "field";
attrField.value = Variables.field;
//The 0,1000 record limit
OA.Attribute attrLimit = new OA.Attribute();
attrLimit.name = "limit";
// set newer-than filter date
oaDate dateNewer = new oaDate();
dateNewer.year = Variables.dtBeginRange.ToString("yyyy");
dateNewer.month = Variables.dtBeginRange.ToString("MM");
dateNewer.day = Variables.dtBeginRange.ToString("dd");
rr.objects = new oaBase[] { dateNewer };
rr.attributes = new OA.Attribute[] { attrFilter, attrField, attrLimit };
return rr;
}
/// <summary>
/// Returns the session service handler
/// </summary>
/// <param name="compMetadata">The dataflow interface</param>
/// <returns>Service handler</returns>
public OAirServiceHandlerService GetServiceHandler(IDTSComponentMetaData100 compMetadata, ref bool authError)
{
//Connect to web service
OAirServiceHandlerService svc = new OAirServiceHandlerService();
//Set timeout
svc.Timeout = Variables.timeout;
InfoComponent(String.Format("Logging into Open Air [{0}]", DateTime.Now), compMetadata);
LoginResult loginResult = svc.login(GetLoginParams());
// Create a new session header object
// Add the session ID returned from the login
SessionHeader header = new SessionHeader();
header.sessionId = loginResult.sessionId;
svc.SessionHeaderValue = header;
return svc;
}
/// <summary>
/// Returns login parameters
/// </summary>
/// <returns>An object that contains the parameters to login to the web service</returns>
public LoginParams GetLoginParams()
{
// create LoginParam object
LoginParams loginParams = new LoginParams();
loginParams.api_namespace = "default";
loginParams.api_key = Variables.key;
loginParams.company = Variables.company;
loginParams.user = Variables.user;
loginParams.password = Variables.password;
loginParams.client = Variables.client;
loginParams.version = Variables.apiVersion;
return loginParams;
}
/// <summary>Outputs an Error</summary>
/// <param name="errorMsg">The error message to send to the UI</param>
/// <param name="compMetadata">The dataflow interface</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="infoMsg">The information message to send to the UI</param>
/// <param name="compMetadata">The dataflow interface</param>
private void InfoComponent(string infoMsg, IDTSComponentMetaData100 compMetadata)
{
bool fail = false;
compMetadata.FireInformation(1, compMetadata.Name, infoMsg, "", 0, ref fail);
}
#endregion
}
#endregion
Before saving and building this code make sure to replace my project name in the code "SC_e49c934c98564fa6be517b883cd15def", with what ever the name of your project is, in the Namespaces region (there's only 2). Now lets step through and explain some of this code, a lot of it I got straight out of the API document. The first thing we do is override CreateNewOutputRows() method: public override void CreateNewOutputRows(). Then we instantiate our ReadRequest object with our query parameters ReadRequest rr = GetReadRequest(). Next, we login to the web service and set up our session: _svc = GetServiceHandler(compMetadata, ref bool authError) and LoginResult loginResult = svc.login(GetLoginParams()). Finally, we set our results object to the result set from the web service results = _svc.read(new ReadRequest[] { rr }) and then loop through all the results and output to the output buffer:
foreach (oaCustomer cust in results[0].objects)
foreach (oaCustomer cust in results[0].objects)
{
Output0Buffer.AddRow();
....
Notice that in this example we're using oaCustomer, if you're looking for another type you would select from the classes provided:
 |
| Figure 12. OpenAir Datatypes |
Let's save, build and run this package and see what we get in our data viewer:
 |
| Figure 13. Data Viewer |
Looking at our progress tab we can view the progress of our package in retrieving our data:
 |
| Figure 14. Progress Tab |
We can also output authentication errors from the web service:
 |
| Figure 15. Progress Tab with Authentication Errors |
As well as read errors:
 |
| Figure 16. Progress Tab with Read Errors |
FailComponent(string.Format("Read Error {0} - {1} [{2}]", err.code, err.text, DateTime.Now), compMetadata);
If our query returns no results, we display that as well:
 |
| Figure 17. Progress Tab with No Records Returned Message |
No comments:
Post a Comment