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

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

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.

Important

This connector is currently in preview. You can try it out and give us feedback. If you want to take a dependency on preview connectors in your solution, please contact Azure support.

Supported capabilities

This QuickBooks connector is supported for the following capabilities:

Supported capabilities	IR
Copy activity (source/-)	① ②
Lookup activity	① ②

① 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 following properties are supported for QuickBooks linked service:

Property	Description	Required
type	The type property must be set to: QuickBooks	Yes
connectionProperties	A group of properties that defines how to connect to QuickBooks.	Yes
Under connectionProperties:
endpoint	The endpoint of the QuickBooks Online server. (that is, quickbooks.api.intuit.com)	Yes
companyId	The company ID of the QuickBooks company to authorize. For info about how to find the company ID, see How do I find my Company ID.	Yes
consumerKey	The client ID of your QuickBooks Online application for OAuth 2.0 authentication. Learn more from here.	Yes
consumerSecret	The client secret of your QuickBooks Online application for OAuth 2.0 authentication. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.	Yes
refreshToken	The OAuth 2.0 refresh token associated with the QuickBooks application. Learn more from here. Note refresh token will be expired after 180 days. Customer need to regularly update the refresh token. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.	Yes
useEncryptedEndpoints	Specifies whether the data source endpoints are encrypted using HTTPS. The default value is true.	No

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:

Property	Description	Required
type	The type property of the dataset must be set to: QuickBooksObject	Yes
tableName	Name of the table.	No (if "query" in activity source is specified)

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:

Property	Description	Required
type	The type property of the copy activity source must be set to: QuickBooksSource	Yes
query	Use the custom SQL query to read data. For example: "SELECT * FROM "Bill" WHERE Id = '123'".	No (if "tableName" in dataset is specified)

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.

Lookup activity properties

To learn details about the properties, check Lookup activity.

Related content

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