Copy data from QuickBooks Online by using Azure Data Factory or Azure Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use the copy activity in an Azure Data Factory or Azure Synapse Analytics pipeline to copy data from QuickBooks Online. It builds on the overview article about the copy activity.

This article also describes how to upgrade the QuickBooks connector from version 1.0 to 2.0. Version 2.0 provides improved native QuickBooks support.

Supported capabilities

The QuickBooks connector is supported for the following capabilities:

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources or sinks, see Supported data stores.

The connector supports QuickBooks OAuth 2.0 authentication.

Getting started

To perform the copy activity with a pipeline, you can use one of the following tools or SDKs:

• Copy Data tool
• Azure portal
• .NET SDK
• Python SDK
• Azure PowerShell
• REST API
• Azure Resource Manager template

Create a linked service to QuickBooks by using the UI

1. In the Azure portal, go to your Azure Data Factory or Azure Synapse workspace.

2. Go to the Manage tab, select Linked services, and then select New.

   • Azure Data Factory
   • Azure Synapse

   

3. Search for QuickBooks, and then select the QuickBooks connector.

   

4. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

You use properties to define Data Factory entities that are specific to the QuickBooks connector.

Linked service properties

The QuickBooks connector now supports version 2.0. To upgrade your QuickBooks connector from version 1.0 to version 2.0, refer to the procedure later in this article. The following sections describe the property details for the two versions.

Version 2.0

The QuickBooks linked service supports the following properties for connector version 2.0:

Here's an example:

{
    "name": "QuickBooksLinkedService",
    "properties": {
        "type": "QuickBooks",
        "version": "2.0",
        "typeProperties": {
            "endpoint": "quickbooks.api.intuit.com",
            "companyId": "<company id>",
            "consumerKey": "<consumer key>", 
            "consumerSecret": {
                 "type": "SecureString",
                 "value": "<clientSecret>"
            },
            "refreshToken": {
                "type": "SecureString",
                "value": "<refresh token>"
            }
        }
    }
}

Version 1.0

The QuickBooks linked service supports the following properties for connector version 1.0:

Here's an example:

{
    "name": "QuickBooksLinkedService",
    "properties": {
        "type": "QuickBooks",
        "typeProperties": {
            "connectionProperties": {
                "endpoint": "quickbooks.api.intuit.com",
                "companyId": "<company id>",
                "consumerKey": "<consumer key>", 
                "consumerSecret": {
                     "type": "SecureString",
                     "value": "<clientSecret>"
              },
                "refreshToken": {
                     "type": "SecureString",
                     "value": "<refresh token>"
              },
                "useEncryptedEndpoints": true
            }
        }
    }
}

Handling refresh tokens for the linked service

When you use the QuickBooks Online connector in a linked service, it's important to manage OAuth 2.0 refresh tokens from QuickBooks correctly.

The linked service uses a refresh token to obtain new access tokens. However, QuickBooks Online periodically updates the refresh token. This action invalidates the previous token.

The linked service doesn't automatically update the refresh token in Azure Key Vault, so you need to manage updating the refresh token to ensure uninterrupted connectivity. Otherwise, you might encounter authentication failures after the refresh token expires.

You can manually update the refresh token in Azure Key Vault based on the QuickBooks Online policy for expiry of refresh tokens. Another approach is to automate updates by using a scheduled task or an Azure function that checks for a new refresh token and updates it in Azure Key Vault.

Dataset properties

For a full list of available sections and properties for defining datasets, see Datasets in Azure Data Factory and Azure Synapse Analytics.

To copy data from QuickBooks Online, set the type property of the dataset to QuickBooksObject. The QuickBooks dataset supports the following properties:

Here's an example:

{
    "name": "QuickBooksDataset",
    "properties": {
        "type": "QuickBooksObject",
        "typeProperties": {},
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<QuickBooks linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see Pipelines and activities in Azure Data Factory and Azure Synapse Analytics. This section provides a list of properties that the QuickBooks source supports.

QuickBooks as source

To copy data from QuickBooks Online, set the source type in the copy activity to QuickBooksSource. The QuickBooks dataset supports the following properties in the copy activity's source section:

Here's an example:

"activities":[
    {
        "name": "CopyFromQuickBooks",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<QuickBooks input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "QuickBooksSource",
                "query": "SELECT * FROM \"Bill\" WHERE Id = '123' "
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

For a full list of available sections and properties for defining activities, see Pipelines and activities in Azure Data Factory and Azure Synapse Analytics.

Copy data from QuickBooks Desktop

The copy activity in the service can't copy data directly from QuickBooks Desktop. To copy data from QuickBooks Desktop, export your QuickBooks data to a comma-separated values (CSV) file and then upload the file to Azure Blob Storage. From there, you can use the service to copy the data to the sink of your choice.

Data type mapping for Quickbooks

When you copy data from QuickBooks, the following mappings apply from the QuickBooks data types to the internal data types that the service uses. To learn about how the copy activity maps the source schema and data type to the sink, see Schema and data type mapping in copy activity.

Lookup activity properties

For details about the properties of the lookup activity, see Lookup activity in Azure Data Factory and Azure Synapse Analytics.

Quickbooks connector lifecycle and upgrade

The following table summarizes information about the versions of the QuickBooks connector:

Upgrade the Quickbooks connector from version 1.0 to version 2.0

1. In the Azure portal, go to your Azure Data Factory or Azure Synapse workspace.

2. Go to the Manage tab, select Linked services, and then select Edit for the linked service.

3. On the Edit linked service pane, select 2.0 for the version. For more information, see the linked service properties for version 2.0 earlier in this article.

4. If you use a SQL query in the copy activity source or the lookup activity that refers to the version 1.0 linked service, you need to convert it to the QuickBooks native query. Learn more about the native query from Copy activity properties earlier in this article and from Query operations and syntax on the Intuit Developer site.

Related content

• For a list of data stores that the copy activity supports as sources and sinks, see Supported data stores and formats.