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 source component dialog. A separate output is setup for the main object and each related composite object.
In this section we will show you how to set up a QuickBooks Source component.
- Before you begin, configure a QuickBooks connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the QuickBooks Source component and drag it onto the Data Flow canvas.
- Double-click on the QuickBooks Source component on the canvas to open the editor.
- Click on the Input and Output Properties tab.
- Remove any extra Outputs that you do not want from the list of outputs. Certain objects offer multiple outputs and they will have many columns duplicated between them. To remove the unwanted Outputs, select the output(s) you do not want, one-by-one, and click on Remove Output for each one.
Use the parameters below to configure the component.
Select an existing QuickBooks connection manager.
Related Topics: QuickBooks Connection Manager
Specify the number of rows to be retrieved as a batch. Default is 1000.
Select a QuickBooks object.
Edition Description Desktop
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:
Specify the WHERE clause of the QuickBooks Online SELECT statement used to retrieve data.
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].[ObjectFilter]. This is the property containing the query.
- Scroll down and find the Expressions property. Set up an expression to modify the query dynamically.
The composite record output contains not only fields from the composite record, but also the parent records. 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 ObjectFilter parameter:
<InvoiceQueryRqType> <IncludeLineItems>true</IncludeLineItems> </InvoiceQueryRqType>
The example above will retrieve item objects associated with the invoice objects.
For additional information about modelling of composite records in SSIS+, please refer to this article.
- New: Support for DataExt object.
- Fixed: Component failed with error "Object reference not set to an instance of an object" when trying to use TxnDeleted object (Thank you, Richard).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.