How to read data from QuickBooks Online in SSIS

Introduction

QuickBooks Online LogoQuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using SSIS in few clicks. We will use SSIS XML Source Connector to Read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth Connection). We will discuss on How to Create an Intuit Developer Account, How to Create QuickBooks Online App for OAuth, We will also discuss reverse scenario to Write data to QuickBooks Online (API POST for Insert or Update in QuickBooks Online) using SSIS REST API Task
In nutshell, this post will focus on how to call QuickBooks Online API using SSIS.
So let’s get started.

Prerequisites

Before we look into Step-By-Step section to extract and load data from QuickBooks Online to SQL Server let’s make sure you met the following requirements.

  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. ZappySys SSIS PowerPack installed. Click on the link to download a FREE trial.
  4. You have the basic familiarity with REST API concepts and QuickBooks Online API.

What is QuickBooks Online

QuickBooks is an accounting software package developed and marketed by Intuit. QuickBooks products are geared mainly toward small and medium-sized businesses and offer on-premises accounting applications as well as cloud-based versions that accept business payments, manage and pay bills, and payroll functions.

Getting Started

In order to start, we will show several examples. ZappySys includes an SSIS XML Source Connector that will help you to call QuickBooks Online API, read various data from QuickBooks Online with SSIS, create a new Contact on QuickBooks Online with SSIS and do many more actions using REST API. To learn more about QuickBooks Online API check this help file.

To access QuickBooks Online data using REST API call, you need to create an Intuit Developer Account and then create QuickBooks Online App.

Create an Intuit Developer Account

Follow the mentioned steps below to create an Intuit developer account:

  1. you can visit this site developer.intuit.com, and click on Sign up from the menu bar.
  2. A registration form will be opened in a Popup. you can fill up and then click on Create Account.
    Signup Form: Intuit Developer Account

    Signup Form: Intuit Developer Account

  3. Complete the validation check and click Continue.
  4. On the confirmation dialog, provide a few details to fill out your profile and click All set.

Your account is created! You’ll automatically be redirected to the app creation page to get coding on your app.

Note

Choose the user ID of your account carefully. Once an app is created in a given account it cannot be moved to another account or deleted. If you are building apps for a client, we recommend you create a dedicated account for that client that stays with them, leaving them full access.

Create a QuickBooks Online App

Follow the steps below to start creating your QuickBooks Online app.

  1. From the developer portal, click on My Apps.
  2. after that, Click Create a new app.

    Create new App: Select APIS

    Create a new App: Select APIS

  3. Click the Select APIs button under Just start coding.
  4. Select the APIs you wish to use. This selection can always be changed later via your app’s settings.
  5. Your new app opens to its Dashboard tab.
  6. Click the Keys tab. This is where you can find all keys related to your app.
    Intuit App: Client Id,Secret Keys for OAuth Connection

    Intuit App: Client Id, Secret Keys for OAuth Connection

  7. To copy a key or token, click its Copy button.

finally, we are ready with an App. Let´s start with an example. In this example, we will show how to retrieve all Invoices data from QuickBooks Online with SSIS.

Load QuickBooks Online data to SQL Server using SSIS XML Source

Now once you registered OAuth app in the QuickBooks Online website we can move to SSIS Part.

Configure OAuth Connection Manager

  1. Right click on Connection Managers Panel to Create New OAuth Connection, and Context Menu will appear, Select New Connection from the Context Menu.
    Connection Manager Panel: Select Connection

    Connection Manager Panel: Select Connection

  2. Select ZS-OAuth Connection Manager from the Connection Managers list.
    SSIS Connection Mangers List: Select O-AUTH Connection Manager

    SSIS Connection Managers List: Select O-AUTH Connection Manager

  3. Configure the new OAuth Connection for QuickBooks Online as below.
    Basically, you need to configure the configurations of App on the General tab (i.e. OAuth Provider, Client ID, Client Secret, Scopes, etc.)

    SSIS OAuth Connection Manager: General Tab Configurations

    SSIS OAuth Connection Manager: General Tab Configurations

  4. also, most importantly, you need to Enter your Callback URL in Advanced Tab.
    SSIS OAuth Connection Manager: Advanced Tab Configurations

    SSIS OAuth Connection Manager: Advanced Tab Configurations

  5. finally, you need to check “Do not include credentials in header
    SSIS OAuth Connection Manager: OAuth2 Grant Options Tab Configurations

    SSIS OAuth Connection Manager: OAuth2 Grant Options Tab Configurations

  6. finally, you are done with the OAuth Configurations. Click on Generate Token button to Generate token and then click on Test Connection button and click on OK to save the connection manager configurations settings.

Configure XML Source Component to read data from QuickBooks Online

  1. First of all, Create a new SSIS Project or Open Existing Project in Visual Studio  (File > New > Project > Business Intelligence > Integration Services)
  2. Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS XML Source Component from the SSIS Toolbox.
    Drag and Drop XML Source Component to read data from QuickBooks Online

    Drag and Drop XML Source Component

  3. furthermore, Double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
    Enter Web URL like this:
    <RequestURL>/v3/company/<realmID>/query?query=<selectStatement>here, We have used 3 placeholders.
    * <RequestURL>
    is either a SandBox base URL or Production base URL.
    * <realmID> is your QuickBooks Online sandbox/production company ID.
    * <selectStatement> can be any query something like select * from invoice where id = ‘239’ (Select statement must be URL Encoded first, For URL Encode you can use ZappySys’s inbuilt functions for URL Encoding See this)
    For more information on Query, an Invoice Request API see this
    You can use Variable too in your Request URL, to make URL Dynamic, See the below-attached Screenshot.
    To make URL dynamic first create an SSIS variable like below screenshot and then change URL to use Variable Placeholder like this
    SSIS XML Source Component: Enter Request URL to read data from QuickBooks Online

    SSIS XML Source Component: Enter Request URL

  4.  After that, Check the Use Credentials checkbox and Select your OAUTH Connection Manager from the drop-down.
    SSIS XML Source Component: Check Use Credentials and Select OAuth Connection Manager to read data from QuickBooks Online

    SSIS XML Source Component: Check Use Credentials and Select OAuth Connection Manager

  5. furthermore, Select the Filter to set the Filter.
    SSIS XML Source Component: Select Filter to read data from QuickBooks Online

    SSIS XML Source Component: Select Filter

  6. It seems like you are all OK with the configurations, Click on Preview button to see the response data.
    SSIS XML Source Component: See Preview to read data from QuickBooks Online

    SSIS XML Source Component: See Preview

  7. finally, Click on OK button to save JSON Source settings.

Configure OLE DB Destination Component for dumping Invoices data into SQL

  1. first of all, Drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. For demo purpose, we will load QuickBooks Online Invoices data into SQL Server Table.
    Drag and Drop OLE DB Destination Component

    Drag and Drop OLE DB Destination Component

  2. Configure OLEDB Destination (Select Connection and Target Table). You can Create a new Table for the destination by clicking a NEW button next to Table drop-down.
    OLE DB Destination Component - Configure for dumping Invoices from QuickBooks Online data in SQL Table

    OLE DB Destination Component – Configure for dumping Invoices from QuickBooks Online data in SQL Table

  3. almost you are done with the configurations, just need to configure mappings, So for that Click on the Columns Tab to configure Mappings. also, Click on OK button to Save the configurations of OLE DB Destination Component.
    OLE DB Destination - Column Configurations

    OLE DB Destination – Column Configurations

  4. finally, Execute your package to Load Invoices data from QuickBooks Online to SQL Server.
    Package executed successfully: Invoices data dumped to SQL

    Package executed successfully: Invoices data dumped into SQL

  5. After that, you can see the dumped records into SQL Table.
    SQL Table: see the inserted records after the complete Package execution

    SQL Table: see the inserted records after the complete Package execution

Create a new Invoice on Quick Books Online (Use SSIS REST API Task)

Now let’s look at one more example to call Quick Books Online REST API. This time we will create a new Invoice using REST API call in SSIS. For this, we will use SSIS REST API Task

  1. First of All, Goto Control Flow Section, Drag and Drop SSIS REST API Task from the SSIS Toolbox.
    Drag and Drop REST API Task

    Drag and Drop REST API Task

  2. furthermore, Double click on that Component to Edit the Configurations and Configure the task like the below-attached screesnshot.
    REST API Task: QuickBooks Online REST API Configurations

    REST API Task: QuickBooks Online REST API Configurations

  3. So all good with the Configurations let’s do Test Request/Response by clicking on Test Request/Response button. See the below-attached request/response screen.
    Create New Invoice in QuickBooks Online Account: Do Test Request/Response

    Create New Invoice in QuickBooks Online Account: Do Test Request/Response

  4. finally, you have created an Invoice in QuickBooks Online successfully using QuickBooks Online’s Intuit REST API and REST API Task, See in QuickBooks Online Site.
    QuickBooks Online Site: See the Newly Created Invoice

    QuickBooks Online Site: See the Newly Created Invoice

  5. And that’s it. similarly, you can Update/Delete particular Invoice in Invoices List in QuickBooks Online.

Conclusion

In this blog, we learned how to read QuickBooks Online Invoices data in SSIS, How to Create an Intuit Developer Account, How to Create QuickBooks Online App for QuickBooks Online REST API OAuth Connection, How to get all Invoices data from QuickBooks Online and create a new Invoice on QuickBooks Online with SSIS using ZS XML Source Component and SSIS REST API Task in a very simple way. You can achieve many more functionalities with the use of both of these tools. Check our blogs/articles on REST API Task https://zappysys.com/blog/tag/ssis-rest-api-task/ and XML Source Component https://zappysys.com/blog/tag/ssis-xml-source/ to find out what these tools are capable of more.

References

Finally, you can use the following links for more information about the use of QuickBooks Online REST API with our tools:

 

Posted in REST API Integration, SSIS REST API Task, SSIS XML Source (File / SOAP) and tagged , , , , , .