ADF Quickbooks Connector: Missing column "Active" -> unable to query inactive Items & Accounts

Jens Eggerath 6 Reputation points
2022-06-09T11:17:04.12+00:00

Hello,

I'm trying to copy data from the Quickbooks Online entities "Item" and "Account" into CSVs on an azure storage account.
When querying directly in the QuickBooks online API explorer, I can run

select * from Account where Active in(true, false)  

and get all accounts including an "Active" column.

When running the same query in the ADF Copy task, I get the following:

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42S22] [Microsoft][SQLEngine] (31750) Column not found: Active,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [42S22] [Microsoft][SQLEngine] (31750) Column not found: Active,Source=QuickBooksODBC_sb64.dll,'  

The problem might come from the unusual behavior of the original QBO SQL API assuming a default filter of Active=true if one does a simple

select * from Account  

So this returns only active accounts!

In Power BI PQ, the QBO connector works with the query

= QuickBooks.Query("SELECT * from Account where Active in(true, false)")  

and returns all columns and all rows inside a list of records.

Regards
Jens

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,245 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.