ADF Quickbooks Connector: Missing column "Active" -> unable to query inactive Items & Accounts
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