{"id":6287,"date":"2019-02-05T13:08:51","date_gmt":"2019-02-05T13:08:51","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6287"},"modified":"2025-04-08T15:47:55","modified_gmt":"2025-04-08T15:47:55","slug":"load-data-exact-online-sql-server-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/","title":{"rendered":"Load data from Exact Online into SQL Server using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In this article, you will learn how to load data from Exact Online<\/a> into SQL Server using SSIS. Exact Online is a popular CRM, ERP and HRM tool which also lets you manage your finances. As an example, we will use Exact Online contacts as data to load it into a SQL Server database. We will use ZappySys SSIS PowerPack<\/a> connector to get contacts from Exact Online and then use standard SSIS connector OLE DB Destination to load data into a table. This SSIS PowerPack connector will be used to get the data:<\/p>\n

 <\/p>\n

 <\/p>\n

\r\n
\"JSON<\/div>\r\n
JSON Source<\/a><\/div>\r\n<\/div><\/div>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Step-by-step – Load data from Exact Online into SQL Server using SSIS<\/h2>\n

    Getting started<\/h3>\n

    We will get these contacts and load them into a SQL Server database:<\/p>\n

    \"\"

    Loading Exact Online contacts into SQL Server<\/p><\/div>\n

    The first thing you will need to do is to create an OAuth application in Exact App Center and then we will proceed in getting the data and loading it into a SQL Server database.<\/p>\n

    Create OAuth application in Exact App Center<\/h3>\n
      \n
    1. Visit Exact App Center<\/a> and login:\n
      \"\"

      Exact App Center login window<\/p><\/div><\/li>\n

    2. Once logged in, click on Manage my apps<\/a> link:\n
      \"\"

      Exact App Center login area<\/p><\/div><\/li>\n

    3. Create a new OAuth application:\n
      \"\"

      Exact Online API applications management page<\/p><\/div><\/li>\n

    4. Give it a name.<\/li>\n
    5. In Redirect URI <\/em>field enter:\u00a0https:\/\/www.zappysyscom\/oauth2
      \n<\/strong><\/p>\n
      \"\"

      Registering Exact Online API application to load data into SQL Server<\/p><\/div><\/li>\n

    6. Hit Register<\/strong>.<\/li>\n
    7. Don’t close the browser, we will use App information later.<\/li>\n<\/ol>\n

      Create OAuth Connection Manager to connect to Exact Online<\/h3>\n
        \n
      1. Create a new SSIS package.<\/li>\n
      2. Create a new OAuth Connection Manager, by right-clicking\u00a0on the Connection Managers pane:\n
        \"\"

        Creating OAuth Connection Manager to connect to Exact Online API application<\/p><\/div><\/li>\n

      3. Select ZS-OAUTH<\/strong> to create one:\n
        \"\"

        Selecting ZS-OAUTH option to create OAuth Connection Manager<\/p><\/div><\/li>\n

      4. Then open your Exact App Center API application configuration we left open in the previous step. We will use that data from that window.<\/li>\n
      5. Select Custom<\/strong> as OAuth Provider.<\/em><\/li>\n
      6. Select Authorization Code Grant<\/strong> as OAuth Grant Type<\/em>.<\/li>\n
      7. Copy Client ID<\/strong> and paste it into Client\u00a0Id<\/em> field.<\/li>\n
      8. Copy Client secret<\/strong> and paste it into Client Secret<\/em> field.<\/li>\n
      9. Set Authorization Url<\/em> to\u00a0https:\/\/start.exactonline.nl\/api\/oauth2\/auth<\/strong>.<\/li>\n
      10. Set Access Token Url<\/em> to\u00a0https:\/\/start.exactonline.nl\/api\/oauth2\/token<\/strong>.
        \n<\/strong><\/p>\n
        \"\"

        Configuring OAuth Connection Manager to access Exact Online data via API<\/p><\/div><\/li>\n

      11. Then open Advanced<\/em> tab and copy paste Redirect URI<\/strong> to Callback\/Return Url<\/em> field:\n
        \"\"

        Configuring OAuth application Callback\/Return URL<\/p><\/div><\/li>\n

      12. Then click on OAuth2 Grant Options<\/em> tab and check option below.<\/li>\n
      13. Finally, since Exact Online refresh token keeps changing, enter a filename where to store the refresh token:\n
        \"\"

        Configuring OAuth2 Grant specifics<\/p><\/div>\n

        NOTE<\/strong>: Can be a non-existent file or must be an empty<\/span>, existing file.<\/div><\/div><\/li>\n<\/ol>\n

        Getting data from Exact Online<\/h3>\n
          \n
        1. Drag and drop Data Flow<\/em> and then JSON Source<\/em> into it:\n
          \"\"

          Dragging and dropping Data Flow Task into Control Flow<\/p><\/div><\/li>\n

        2. Double click on JSON Source<\/em> to modify it.<\/li>\n
        3. Check\u00a0Use Credentials<\/strong> checkbox and select OAuth 1<\/strong> as your Connection Manager<\/em>.<\/li>\n
        4. Add HTTP Header<\/em> named Accept<\/strong>\u00a0and set the\u00a0value to application\/json<\/strong>.<\/li>\n
        5. In Array Filter<\/em> field enter $.d.results[*]<\/strong>.<\/li>\n
        6. In Enter Path or Web URL<\/em> field enter this URL:
          \nhttps:\/\/start.exactonline.nl\/api\/v1\/current\/Me?$select=CurrentDivision<\/strong><\/li>\n
        7. Hit Preview<\/strong> button.<\/li>\n
        8. Copy your division number, you will need it when constructing a URL for getting contacts.\n
          \"\"

          Getting division number<\/p><\/div><\/li>\n

        9. Now enter this URL in the field:
          \nhttps:\/\/start.exactonline.nl\/api\/v1\/{division}<\/span>\/crm\/Contacts?$select=FirstName,LastName
          \n<\/strong>Replace {division}<\/em> with your division number.<\/li>\n<\/ol>\n
          \"\"

          Configuring JSON Source to get Exact Online data and load it into SQL Server<\/p><\/div>\n

          NOTE:<\/strong> Check this link to construct a URL to get other fields than FirstName, Lastname\u00a0<\/em>as well: https:\/\/start.exactonline.nl\/docs\/HlpRestAPIResourcesDetails.aspx?name=CRMContacts<\/a>.<\/div><\/div>\n

          Pagination<\/h4>\n

          To paginate through the results, perform these steps:<\/p>\n

            \n
          1. Open Pagination<\/em> tab.<\/li>\n
          2. In Next Link\/Cursor Expression<\/em> field enter\u00a0$.d.__next<\/strong><\/li>\n<\/ol>\n
            \"\"

            Configuring pagination in JSON Source to paginate through Exact Online contacts<\/p><\/div>\n

            NOTE:<\/strong>\u00a0More on pagination: https:\/\/support.exactonline.com\/community\/s\/knowledge-base#All-All-DNO-Content-resttips<\/a>.<\/div><\/div>\n

            Loading Exact Online data into SQL Server<\/h3>\n
              \n
            1. Drag and drop OLE DB Destination<\/em> or similar connector.<\/li>\n
            2. Connect JSON Source<\/em> with OLE DB Destination<\/em>.<\/li>\n
            3. Create a new connection.<\/li>\n
            4. Click New<\/strong>\u00a0to create a new table.<\/li>\n
            5. Hit OK<\/strong>.<\/li>\n
            6. Select Mappings<\/strong> to map the columns.<\/li>\n
            7. Hit OK<\/strong> to close the window.<\/li>\n
            8. Execute the package.<\/li>\n<\/ol>\n
              \"\"

              Configuring OLE DB Destination to load Exact Online data into SQL Server<\/p><\/div>\n

              The results<\/h3>\n

              Once you execute the package, you should see similar results in Visual Studio and SQL Server Management Studio:<\/p>\n

              \"\"

              Executing SSIS package and loading Exact Online data\u00a0 into SQL Server<\/p><\/div>\n

              \"\"

              Successfully loading Exact Online data into SQL Server<\/p><\/div>\n

              As you see, these are the same Exact Online contacts showed in the Getting Started section.<\/p>\n

              NOTE:<\/strong>\u00a0Check more API methods you can use to get data from Exact Online: https:\/\/start.exactonline.nl\/docs\/HlpRestAPIResources.aspx?SourceAction=10<\/a><\/div><\/div>\n

              Conclusion<\/h2>\n

              You learned how to load data from Exact Online into SQL Server using SSIS and ZappySys SSIS PowerPack<\/a>. At first, we created an Exact Online OAuth application, then created an OAuth Connection Manager based on its information and finally loaded contacts using JSON Source.<\/p>\n

              References<\/strong><\/h2>\n

              Exact App Center<\/a><\/p>\n

              API methods<\/a><\/p>\n

              Get contacts API method<\/a><\/p>\n

              Pagination<\/a><\/p>\n

               <\/p>\n

               <\/p>\n","protected":false},"excerpt":{"rendered":"

              Introduction In this article, you will learn how to load data from Exact Online into SQL Server using SSIS. Exact Online is a popular CRM, ERP and HRM tool which also lets you manage your finances. As an example, we will use Exact Online contacts as data to load it into a SQL Server database. […]<\/p>\n","protected":false},"author":3,"featured_media":6441,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[176,225,16,17,205,8],"tags":[449,450,6,271,3],"yoast_head":"\r\nLoad data from Exact Online into SQL Server using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Load data from Exact Online into SQL Server using SSIS - ZappySys Blog\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Load data from Exact Online into SQL Server using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Load data from Exact Online into SQL Server using SSIS - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2019-02-05T13:08:51+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-04-08T15:47:55+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"200\" \/>\r\n\t<meta property=\"og:image:height\" content=\"200\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/\",\"name\":\"Load data from Exact Online into SQL Server using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png\",\"datePublished\":\"2019-02-05T13:08:51+00:00\",\"dateModified\":\"2025-04-08T15:47:55+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c146068d4cb053724ae5ce85759c3ada\"},\"description\":\"Load data from Exact Online into SQL Server using SSIS - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png\",\"width\":200,\"height\":200},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Load data from Exact Online into SQL Server using SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c146068d4cb053724ae5ce85759c3ada\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"url\":\"https:\/\/zappysys.com\/blog\/author\/gliaskus\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Load data from Exact Online into SQL Server using SSIS | ZappySys Blog","description":"Load data from Exact Online into SQL Server using SSIS - ZappySys Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/","og_locale":"en_US","og_type":"article","og_title":"Load data from Exact Online into SQL Server using SSIS | ZappySys Blog","og_description":"Load data from Exact Online into SQL Server using SSIS - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/","og_site_name":"ZappySys Blog","article_published_time":"2019-02-05T13:08:51+00:00","article_modified_time":"2025-04-08T15:47:55+00:00","og_image":[{"width":200,"height":200,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/","url":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/","name":"Load data from Exact Online into SQL Server using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png","datePublished":"2019-02-05T13:08:51+00:00","dateModified":"2025-04-08T15:47:55+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c146068d4cb053724ae5ce85759c3ada"},"description":"Load data from Exact Online into SQL Server using SSIS - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/02\/exact-online-logo-1.png","width":200,"height":200},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/load-data-exact-online-sql-server-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Load data from Exact Online into SQL Server using SSIS"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/c146068d4cb053724ae5ce85759c3ada","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/60165d46d0b33aefb91df61a22b0e1f4?s=96&d=mm&r=g","caption":"ZappySys"},"url":"https:\/\/zappysys.com\/blog\/author\/gliaskus\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6287"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=6287"}],"version-history":[{"count":16,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6287\/revisions"}],"predecessor-version":[{"id":11353,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/6287\/revisions\/11353"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/6441"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=6287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=6287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=6287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}