QuickBooks Source

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Join VIP ----- Questions?

Overview

The QuickBooks Source Component is an SSIS Data Flow Component for consuming data from the QuickBooks service. The configuration parameters are provided in the standard data flow component dialog. A separate output is setup for the main object and each related composite object.


Demonstration

Demonstration VideoDemonstration Video

Setup

Use the parameters below to set up the component.

Parameters

QUICKBOOKS

Select an existing QuickBooks connection manager.

Related Topics: QuickBooks Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch. Default is 1000.

Object

Select a QuickBooks object.

QueryXML

Specify query XML. The query should be the same XML format used to serialize QuickBooks query. For example to retrieve all employees with a name starting with "John", use the following query:

<EmployeeQueryRqType>
  <NameFilter>
    <MatchCriterion>StartsWith</MatchCriterion>
    <Name>John</Name>
  </NameFilter>
</EmployeeQueryRqType>

Composite object data

The composite object output contains not only fields from the composite object, but also the parent objects. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with a blank row (all NULLs).

In order to retrieve the composite data for the Quickbooks Desktop version, you must include an element similar to the following in the QueryXML parameter:

<InvoiceQueryRqType>           
  <IncludeLineItems>true</IncludeLineItems>
</InvoiceQueryRqType>

The example above will retrieve item objects associated with the invoice objects.

How to modify source query dynamically at runtime

The source object, query and parent are available in the data flow properties list. Follow these steps to set up an expression:

  • Right-click on the data flow canvas and select the Properties menu.
  • Scroll down and find the property named like [QuickBooks Source].[QueryXML]. This is the property containing the query.
  • Scroll down and find the Expressions property. Set up an expression to modify the query dynamically.