Copy data from QuickBooks Online using Azure Data Factory or Synapse Analytics (Preview)

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use the Copy Activity in an Azure Data Factory or Synapse Analytics pipeline to copy data from QuickBooks Online. It builds on the copy activity overview article that presents a general overview of copy activity.

Supported capabilities

This 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/sinks, see the Supported data stores table.

This 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:

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

Create a linked service to QuickBooks using UI

Use the following steps to create a linked service to QuickBooks in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for QuickBooks and select the QuickBooks connector.

   

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

   

Connector configuration details

The following sections provide details about properties that are used to define Data Factory entities specific to QuickBooks connector.

Linked service properties

The QuickBooks connector now supports version 2.0 (Preview). Refer to this section to upgrade your QuickBooks connector version from version 1.0. For the property details, see the corresponding sections.

• Version 2.0 (Preview)
• Version 1.0

Version 2.0 (Preview)

The QuickBooks linked service supports the following properties when apply version 2.0 (Preview):

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 following properties are supported for QuickBooks linked service when apply version 1.0:

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, invalidating the previous one. The linked service does not 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 once the refresh token expires.

You can manually update the refresh token in Azure Key Vault based on QuickBooks Online's refresh token expiry policy. But another approach is to automate updates with a scheduled task or Azure Function that checks for a new refresh token and updates it in Azure Key Vault.

Dataset properties

For a full list of sections and properties available for defining datasets, see the datasets article. This section provides a list of properties supported by QuickBooks dataset.

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

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 the Pipelines article. This section provides a list of properties supported by QuickBooks source.

QuickBooks as source

To copy data from QuickBooks Online, set the source type in the copy activity to QuickBooksSource. The following properties are supported in the copy activity source section:

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>"
            }
        }
    }
]

Copy data from Quickbooks Desktop

The Copy Activity in the service cannot 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 Quickbooks's data types to the internal data types used by the service. To learn about how the copy activity maps the source schema and data type to the sink, see Schema and data type mappings.

Lookup activity properties

To learn details about the properties, check Lookup activity.

Quickbooks connector lifecycle and upgrade

The following table shows the release stage and change logs for different versions of the QuickBooks connector:

Upgrade the Quickbooks connector from version 1.0 to version 2.0 (Preview)

1. In Edit linked service page, select 2.0 (Preview) for version. For more information, see linked service version 2.0 (Preview) properties.
2. If you use SQL query in the copy activity source or the lookup activity that refers to the version 1.0 linked service, you need to convert them to the QuickBooks native query. Learn more about native query from Quickbooks as a source type and query operations and syntax.
3. The data type mapping for the Quickbooks linked service version 2.0 (Preview) is different from that for the version 1.0. To learn the latest data type mapping, see Data type mapping for Quickbooks.

Related content

For a list of data stores supported as sources and sinks by the copy activity, see supported data stores.