{"id":4763,"date":"2018-09-08T06:25:14","date_gmt":"2018-09-08T06:25:14","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4763"},"modified":"2025-04-08T15:46:29","modified_gmt":"2025-04-08T15:46:29","slug":"read-data-quickbooks-online-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-data-quickbooks-online-using-ssis\/","title":{"rendered":"How to read data from QuickBooks Online in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"QuickBooks<\/a>QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement\u00a0QuickBooks Online API<\/a>\u00a0Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using\u00a0SSIS<\/strong>\u00a0in few clicks. We will use\u00a0SSIS XML Source<\/a>\u00a0to\u00a0Read data from QuickBooks Online<\/strong>\u00a0and 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\u00a0Write data to QuickBooks Online<\/strong>\u00a0(API POST for Insert or Update in QuickBooks Online) using SSIS REST API Task<\/a>
\nIn nutshell,\u00a0this post will focus on how to call QuickBooks Online API using SSIS.
\nSo let\u2019s get started.<\/p>\n

Prerequisites<\/span><\/h2>\n

Before we look into Step-By-Step section to extract and load data from\u00a0QuickBooks Online to SQL Server<\/strong>\u00a0let\u2019s make sure you met the\u00a0following requirements.<\/p>\n

    \n
  1. SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. ZappySys SSIS PowerPack<\/em>\u00a0installed<\/a>. Click on the link to download a FREE trial.<\/li>\n
  4. You have the basic familiarity with REST API concepts and QuickBooks Online API.<\/li>\n<\/ol>\n

    What is QuickBooks Online<\/h2>\n

    QuickBooks<\/b>\u00a0is an accounting software package developed and marketed by Intuit.\u00a0QuickBooks 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.<\/p>\n

    Getting Started<\/h2>\n

    In order to start, we will show several examples. ZappySys includes an\u00a0SSIS XML Source<\/a>\u00a0that 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<\/a>.<\/p>\n

    To access QuickBooks Online data using REST API call, you need to create an Intuit Developer Account and then create QuickBooks Online App.<\/p>\n

    Create an Intuit Developer Account<\/h3>\n

    Follow the mentioned steps below to create an Intuit developer account:<\/p>\n

      \n
    1. you can visit this site\u00a0developer.intuit.com<\/a>, and click on Sign up from the menu bar.<\/li>\n
    2. A registration form will be opened in a Popup. you can fill up and then click on Create Account.\n
    3. Complete the validation check and click\u00a0Continue<\/strong>.<\/li>\n
    4. On the confirmation dialog, provide a few details to fill out your profile and click\u00a0All set<\/strong>.<\/li>\n<\/ol>\n
      \n

      Your account is created! You’ll automatically be redirected to the\u00a0app creation page<\/a>\u00a0to get coding on your app.
      \n

      Note<\/strong><\/p>\n

      Choose the user ID\u00a0of your account carefully. Once an app is created in a given account\u00a0it cannot be moved to another account or deleted. If you are building apps for a\u00a0client, we recommend you create a dedicated account for that client that stays with them, leaving them full access.<\/div><\/div>\n

      Create a QuickBooks Online App<\/h3>\n

      Follow the steps below to start creating your QuickBooks Online app.<\/p>\n

        \n
      1. From the\u00a0developer<\/a>\u00a0portal, click on\u00a0My Apps<\/strong>.<\/li>\n
      2. after that, Click\u00a0Create a new app.
        \n<\/strong><\/strong><\/p>\n
      3. Click the\u00a0Select APIs<\/strong>\u00a0button under Just start coding.<\/li>\n
      4. Select the APIs you wish to use. This selection can always be changed later via your\u00a0app’s settings<\/a>.<\/li>\n
      5. Your new app opens to its Dashboard tab.<\/li>\n
      6. Click the\u00a0Keys<\/strong>\u00a0tab. This is where you can find all keys related to your app.\n
      7. To copy a key or token, click its\u00a0Copy<\/strong>\u00a0button.<\/li>\n<\/ol>\n

        finally, we are ready with an App. Let\u00b4s start with an example. In this example, we will show how to retrieve all Invoices data from QuickBooks Online with SSIS.<\/p>\n

        Load QuickBooks Online data to SQL Server using SSIS XML Source<\/h3>\n

        Now once you registered OAuth app in the QuickBooks Online website we can move to SSIS Part.<\/p>\n

        Configure OAuth Connection Manager<\/h4>\n
          \n
        1. Right click on Connection Managers Panel to Create New OAuth Connection, and Context Menu will appear, Select New Connection from the Context Menu.\n
        2. Select ZS-OAuth Connection Manager from the Connection Managers list.\n
        3. Configure the new OAuth Connection for QuickBooks Online<\/strong> as below.
          \nBasically, you need to configure the configurations of App on the General tab (i.e. OAuth Provider, Client ID, Client Secret, Scopes, etc.)<\/p>\n
        4. also, most importantly, you need to Enter your Callback URL in Advanced Tab.\n
        5. finally, you need to check “Do not include credentials in header<\/strong>”\n
        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.<\/li>\n<\/ol>\n

          Configure XML Source Component to\u00a0read data from QuickBooks Online<\/h4>\n
            \n
          1. First of all, Create a new SSIS Project or Open Existing Project in Visual Studio\u00a0 (File > New > Project > Business Intelligence > Integration Services)<\/li>\n
          2. Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS XML Source Component<\/a> from the SSIS Toolbox.\n
          3. furthermore, Double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
            \nEnter Web URL like this:
            \n<RequestURL>\/v3\/company\/<realmID>\/query?query=<selectStatement><\/span><\/code>here,\u00a0We have used 3 placeholders.
            \n* <RequestURL><\/strong>is either a SandBox base URL or Production base URL.<\/p>\n
            Sandbox Base URL: https:\/\/sandbox-quickbooks.api.intuit.com\r\nProduction Base URL: https:\/\/quickbooks.api.intuit.com<\/pre>\n
            * <realmID> <\/strong>is\u00a0your QuickBooks Online sandbox\/production\u00a0company ID<\/strong>.
            \n* <selectStatement> <\/strong>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<\/a>)
            \nFor more information on Query, an Invoice Request API\u00a0            see this<\/a>
            \n<\/strong>You can use Variable too in your Request URL, to make URL Dynamic, See the below-attached Screenshot.
            \nTo make URL dynamic first create an SSIS variable like below screenshot and then change URL to use             Variable Placeholder<\/a> like this<\/p>\n
            https:\/\/sandbox-quickbooks.api.intuit.com\/v3\/company\/{{User::CompanyID}}\/query?query={{User::SelectQuery,FUN_URLENC}}<\/pre>\n
            \"SSIS<\/a>
            SSIS XML Source Component: Enter Request URL<\/p><\/div><\/li>\n
          4. \u00a0After that, Check the Use Credentials<\/strong> checkbox and Select your OAUTH Connection Manager from the drop-down.\n
          5. furthermore, Select the Filter to set the Filter.\n
          6. It\u00a0seems like you are all OK with the configurations, Click on Preview button to see the response data.\n
          7. finally, Click on OK button to save JSON Source settings.<\/li>\n<\/ol>\n
            Configure OLE DB Destination Component for dumping Invoices data into SQL<\/h4>\n
              \n
            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.\n
            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.\n
            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.\n
            4. finally, Execute your package to Load Invoices data from QuickBooks Online to SQL Server.\n
            5. After that, you can see the dumped records into SQL Table.\n
              \"SQL<\/a>
              SQL Table: see the inserted records after the complete Package execution<\/p><\/div><\/li>\n<\/ol>\n
              Create a new Invoice on Quick Books Online (Use SSIS REST API Task)<\/h2>\n
              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\u00a0SSIS REST API Task<\/a><\/p>\n
                \n
              1. First\u00a0of All, Goto Control Flow Section, Drag and Drop\u00a0SSIS REST API Task<\/a>\u00a0from the SSIS Toolbox.\n
              2. furthermore, Double click on that Component to Edit the Configurations and Configure the task like the below-attached screesnshot.\n
                ::::: Request Settings :::::\r\n\r\n* Select Request URL Access Mode to select Url from Connection.\r\n* Select Url Connection: Select your created OAUTH Connection Manager.\r\n* Enter Reqeust URL\r\n      Request URL: POST <RequestURL>\/v3\/company\/<realmID>\/invoice\r\n      here, \r\n      <RequestURL> is either a SandBox base URL OR Production base URL.\r\n      SandBox base URL: https:\/\/sandbox-quickbooks.api.intuit.com\r\n      Production base URL: https:\/\/quickbooks.api.intuit.com\r\n      <realmID> is your QuickBooks Online sandbox\/production company ID.\r\n* Select HTTP Request Method as POST method\r\n* Enter Request Body.\r\n      Request Body:\r\n      {\r\n\t\"Line\": [{\r\n\t\t\"Amount\": 100.00,\r\n\t\t\"DetailType\": \"SalesItemLineDetail\",\r\n\t\t\"SalesItemLineDetail\": {\r\n\t\t\t\"ItemRef\": {\r\n\t\t\t\t\"value\": \"1\",\r\n\t\t\t\t\"name\": \"Services\"\r\n\t\t\t},\r\n\t\t\t\"TaxCodeRef\": {\r\n\t\t\t\t\"value\":\"20\"\r\n\t\t\t}\r\n\t\t}\r\n\t}],\r\n\t\"CustomerRef\": {\r\n\t\t\"value\": \"1\"\r\n\t}\r\n      }\r\n* Select Body Content Type: application\/json\r\n(For more Information See this\u00a0QuickBooks Online API Documentation for Creating\u00a0a\u00a0new\u00a0Invoice, \r\nhttps:\/\/developer.intuit.com\/docs\/api\/accounting\/invoice)<\/pre>\n
                \"REST<\/a>
                REST API Task: QuickBooks Online REST API Configurations<\/p><\/div><\/li>\n
              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.\n
              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.\n
              5. And that’s it.\u00a0similarly, you can Update\/Delete particular Invoice in Invoices List in QuickBooks Online.<\/li>\n<\/ol>\n
                Conclusion<\/span><\/h2>\n
                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\u00a0ZS XML Source Component<\/a>\u00a0and\u00a0SSIS REST API Task<\/a> 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\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-rest-api-task\/<\/a>\u00a0and XML Source Component\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-xml-source\/<\/a>\u00a0to find out what\u00a0these tools<\/em>\u00a0are capable of more.<\/p>\n
                References<\/span><\/h2>\n
                Finally, you can use the following links for more information about the use of QuickBooks Online REST API with our tools:<\/p>\n