How to read data from QuickBooks Online in SSIS

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

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

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

2. Select ZS-OAuth Connection Manager from the Connection Managers list.
   

   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

4. also, most importantly, you need to Enter your Callback URL in Advanced Tab.
   

   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

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

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.
   
   Sandbox Base URL: https://sandbox-quickbooks.api.intuit.com Production Base URL: https://quickbooks.api.intuit.com

   1 2	Sandbox Base URL: https://sandbox-quickbooks.api.intuit.com Production Base URL: https://quickbooks.api.intuit.com

   * <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
   
   https://sandbox-quickbooks.api.intuit.com/v3/company/{{User::CompanyID}}/query?query={{User::SelectQuery,FUN_URLENC}}

   1	https://sandbox-quickbooks.api.intuit.com/v3/company/{{User::CompanyID}}/query?query={{User::SelectQuery,FUN_URLENC}}

   

   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

5. furthermore, Select the Filter to set the Filter.
   

   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

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

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

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

4. finally, Execute your package to Load Invoices data from QuickBooks Online to SQL Server.
   

   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

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

2. furthermore, Double click on that Component to Edit the Configurations and Configure the task like the below-attached screesnshot.
   
   ::::: Request Settings ::::: * Select Request URL Access Mode to select Url from Connection. * Select Url Connection: Select your created OAUTH Connection Manager. * Enter Reqeust URL Request URL: POST <RequestURL>/v3/company/<realmID>/invoice here, <RequestURL> is either a SandBox base URL OR Production base URL. SandBox base URL: https://sandbox-quickbooks.api.intuit.com Production base URL: https://quickbooks.api.intuit.com <realmID> is your QuickBooks Online sandbox/production company ID. * Select HTTP Request Method as POST method * Enter Request Body. Request Body: { "Line": [{ "Amount": 100.00, "DetailType": "SalesItemLineDetail", "SalesItemLineDetail": { "ItemRef": { "value": "1", "name": "Services" }, "TaxCodeRef": { "value":"20" } } }], "CustomerRef": { "value": "1" } } * Select Body Content Type: application/json (For more Information See this QuickBooks Online API Documentation for Creating a new Invoice, https://developer.intuit.com/docs/api/accounting/invoice)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

   ::::: Request Settings :::::   * Select Request URL Access Mode to select Url from Connection. * Select Url Connection: Select your created OAUTH Connection Manager. * Enter Reqeust URL       Request URL: POST <RequestURL>/v3/company/<realmID>/invoice       here,       <RequestURL> is either a SandBox base URL OR Production base URL.       SandBox base URL: https://sandbox-quickbooks.api.intuit.com       Production base URL: https://quickbooks.api.intuit.com       <realmID> is your QuickBooks Online sandbox/production company ID. * Select HTTP Request Method as POST method * Enter Request Body.       Request Body:       { "Line": [{ "Amount": 100.00, "DetailType": "SalesItemLineDetail", "SalesItemLineDetail": { "ItemRef": { "value": "1", "name": "Services" }, "TaxCodeRef": { "value":"20" } } }], "CustomerRef": { "value": "1" }       } * Select Body Content Type: application/json (For more Information See this QuickBooks Online API Documentation for Creating a new Invoice, https://developer.intuit.com/docs/api/accounting/invoice)

   

   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

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

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:

• QuickBooks Online Documentation for REST API
• Landing Page for REST API Task, you can also find Tutorial Video here.
• Landing Page for XML Source Component, you can also find Tutorial Video here.
• Help File: Documentation of SSIS REST API Task and XML Source Component.