qbo.Import

The Import module provides an infrastructure for mapping and importing data into QBO.

Import Framework

The import framework is a series of patterns and methods that allow importing of any QBO data via XML. This is the most flexible method of automating data ingestion into a QBO system. There are other methods, and the IImport interface allows you to create new classes to handle your own methods of data ingestion and ETL.

IImport interface

There are a variety of ways to import data into SQL server, and the IImport interface provides the scaffolding to support all of them. The three most common methods are:
  • BCP: SQL server can directly read data from external files, and insert or update QBO data accordingly. This is fast, but bypasses application-tier functionality
  • XML: the native QBO classes support deserialization of XML into QBO classes, and can invoke any application tier functionality in the process of processing XML data
  • Spreadsheets: The Excel batch engine is designed to process spreadsheets via the application tier, and includes several convenience functions to make it easy to 'script' application tier calls via a spreadheet

Remote Monitoring

A typical use case is remote monitoring of a FTP site for file to import. For example:

ImportFile.ashx/Queue/Watch?FileObject=SomeClientFtpSite&Path=/Outbound&Pattern=*.csv&TemplateID=X&Schedule=Daily at 3am

will create a job to check the FTP site configured in SomeClientFtpSite every day at 3 am. When invoking the watch method, an ImportFile record will be created for each file in the target location. By default, the file will be copied to the 'Database' FileObject (in case SQL Server needs to read it directly), and the original file will be deleted from the target location.

Parameters available to this method include:
  • FileObject (required): the name of the FileObject (file repository) to monitor
  • Path (optional, defaults to /): the folder to monitor
  • Pattern (optional, defaults to *): the naming pattern files to be imported must follow to be imported
  • Depth (optional, defaults to 0): depth of child folders to parse looking for files to import
  • MoveTo (optional, defaults to Database): name of the FileObject to move the file to before deleting the original
  • TransferTo (optional, default to null): name of the FileObject to copy the file to; the original will be left alone
  • Async (optional, defaults to true): when true, the import will be queued for async processing
  • TemplateID (required): the ImportFileTemplateID to use when creating 
The sequence of execution is:
  • FileObject.List() is called to retrieve a list of files available for import
  • for each file:
    • create an Attachment
    • create an ImportFile, setting the PreTransformID to point to the new Attachment
    • if a TransferTo parameter was specified, copy the file, otherwise move the file (deleting the original)
    • if async, queue ImportFile.Import(), otherwise invoke ImportFile.Import

Packages

Packages are QBO 3 'add-ons' that provide pre-configured functionality in QBO 3. Package files are simply QBO 3 compliant XML files that pre-seed data and configuration entries to rapidly set up functionality. For example, the Compliance package will install the data and configuration required to manage compliance audits for attorneys in the mortgage space.

To view packages available for install, navigate to Design > Configuration > Packages (Import/ImportFile.ashx/PackageList).
To install a packages, click on the name of the package.

Creating packages

Developers can create packages by:
  • Creating a Theme (empty ASP.NET web project)
  • Adding the required XSLTs, javascript, and css files to the project
  • Creating an import-framework compliant XML documents, placed in Config/Setup.{Package Name}.xml
  • Deploying the project to the target QBO 3 system.
Note that custom configuration components (statements, filters, file objects, etc.) should be part of the Setup XML file, so they are stored in the ConfigurationEntry table instead of 'polluting' the core configuration files.

See Source: Theme.Compliance.csproj for an example package.

Bulk Applying a Method Signature 

The import module's ImportFile class includes an Apply method that supports applying a method signature to all rows returned by a query. Examples include:

// Add a message to all Contacts with a Beverly Hills zip code
Apply.ashx/Contact/Search?PostalCode=90210&Signature=Message/Save?Object=Contact%26&ObjectID={ContactID}&Subject=Welcome to the Jungle, {FirstName}
- or - 
ImportFile.ashx/Apply?ClassName=Contact&Operation=Search&PostalCode=90210&Signature=Message/Save?Object=Contact%26&ObjectID={ContactID}&Subject=Welcome to the Jungle, {FirstName}

// Launch a workflow for Loans with a UPB of more than $1M
Apply.ashx/Loan/Search?UPBAmount>=1000000&Signature=Decision/Save?Object=Contact%26&ObjectID={LoanID}&Template=High Risk Loan Analysis
- or -
ImportFile.ashx/Apply?ClassName=Loan&Operation=Search&UPBAmount>=1000000&Signature=Decision/Save?Object=Contact%26&ObjectID={LoanID}&Template=High Risk Loan Analysis

Notes:
  • The Signature often needs to include ampersands (&): the HTML escape sequence for this is %26
  • Javascript can field this easily: escape('Decision/Save?Object=Loan&ObjectID={LoanID}')

Import File Status

The Import module's commonly used status values include:
  • Awaiting Import: set upon calling ImportFile/Import
  • Awaiting Validation: set upon calling ImportFile/Validate.
  • Stored: set upon calling ImportFile/Create when the template is not set to Async
    • ImportFile/Create is called by dragging-and-dropping files on the Import File dashboard, or by calling ImportFile/Watch
  • Error: set when an error occurred during many of the import method signatures
  • Validation Error: set when validation failed after calling ImportFile/Import, when the template has a validator defined
  • Complete: set when the import is complete
    • for async engines like the Batch Engine, this is handled by a callback, and may take minutes or hours.
  • Pending: set by ImportFile/Import when the underlying engine does not complete its work
    • for example, the Batch Engine will queue an ImportFileQueue/Process method signature for each row imported
  • Ending:  set by ImportFile/EndImport, while any workflows are launched at the end of the import process
  • Queued: set when the import is waiting to be processed by Queueing
  • Validated: set when ImportFile/Validate successfully validates the data being imported
  • Validation Failed: set when ImportFile/Validate fails to validate the data being imported