{"id":1602,"date":"2017-07-25T03:02:24","date_gmt":"2017-07-25T03:02:24","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1602"},"modified":"2025-03-31T13:44:42","modified_gmt":"2025-03-31T13:44:42","slug":"reading-loading-data-in-xero-sql-server-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/","title":{"rendered":"How to read\/load data in Xero using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>Xero is well known Cloud based Accounting Software. In this post you will learn how to implement Xero API<\/a> Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using SSIS<\/strong> in few clicks. We will use SSIS JSON Source<\/a> to Read data from Xero<\/strong> and Load into SQL Server \/ other target (Using OAuth Connection). We will also discuss reverse scenario to Write data to Xero<\/strong> (API POST for Insert or Update in Xero) using SSIS Web API Destination<\/a> and SSIS JSON Generator Transform<\/a><\/p>\n

<\/h2>\n

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

Before we look into Step-By-Step section to extract and load data from Xero to SQL Server<\/strong> let\u2019s make sure you met following 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 FREE trial.<\/li>\n
  4. You have basic familiarity with REST API concepts and Xero API.<\/li>\n<\/ol>\n

    Concepts of Xero REST API<\/h2>\n

    API Authorization<\/h3>\n

    Before you can access Xero API you must register account and App. There are 3 types of Apps to access Xero API.<\/p>\n

      \n
    1. Private App (X509 Certificate used to sign requests)<\/li>\n
    2. Public App (Use 3-legged OAuth. Token expires in 30 mins and then you have to authorize again from UI)<\/li>\n
    3. Partner App (Use 3-legged OAuth. Token expires in 30 mins.. but refresh token supported to get new token for unlimited times without Authorization via Login UI)<\/li>\n
    4. OAuth2.0 Authentication\u00a0(Recommended)\u00a0<\/a><\/li>\n<\/ol>\n

      In this article we will use Private App<\/a> to access your company data in Xero.<\/p>\n

      Data formats<\/h3>\n

      Xero API supports Xml and JSON both formats over the same URL (endpoints). If you want data in JSON format then you have to pass following Header (else data in Xml format will be returned). If you are using SSIS JSON Source<\/a> then make sure following header is added. For SSIS XML Source<\/a> no need to add this header.<\/p>\n

      Accept : application\/json<\/pre>\n
      Step-By-Step : Xero API Integration in SSIS<\/h2>\n
      Now lets look at steps needed to fetch data from Xero and load into SQL Server.<\/p>\n
      Configure OAuth2.0 Connection for Xero API\u00a0(Recommended)<\/span><\/h3>\n
      So let’s how to create the Xero OAuth2.0 connection. For more on OAuth2.0 refer to this article<\/a>.<\/p>\n
        \n
      1. Right-click inside the Connection Managers area again and click \u201cNew Connection\u2026\u201d<\/li>\n
      2. From the connection type list select \u201cZS-OAUTH\u201d connection type.\n
      3. Select\u00a0OAuth Provider\u00a0<\/em>which Custom, enter the CleintID and Secret and set the URLs as below and desired scopes<\/a>\u00a0that are required for the API Call.
        \nAuth URL:<\/p>\n
        https:\/\/login.xero.com\/identity\/connect\/authorize<\/pre>\n
        Token URL:<\/p>\n
        https:\/\/identity.xero.com\/connect\/token<\/pre>\n
        Scopes: Add your desired scopes based on your API call requirements.
        \n
        Note: To get a refresh token, you must request the offline_access scope. A refresh token allows you to refresh your access token and maintain an offline connection.
        \noffline_access<\/strong><\/p>\n
        https:\/\/developer.xero.com\/documentation\/guides\/oauth2\/scopes\/#offline-access<\/a><\/div><\/div>\n
        \"OAuth2.0<\/a>
        OAuth2.0 Connection<\/p><\/div><\/li>\n
      4. \u00a0Now go to the Advanced tab and set the redirect URL as below and also you need to set the same URL in the Xero API App.\n
        https:\/\/zappysys.com\/oauth2<\/pre>\n
        \"\"<\/a>
        Set Callback\/Return Url in OAuth Connection<\/p><\/div><\/li>\n
      5. Now Click on the Generate Token Button and logged in with Xero username and password and grant permission.<\/li>\n
      6. That’s it your token is generated. If you got the blank Screen like this after following all the steps. Close that window.\n
      7. And it will ask you to use the\u00a0Different Method to generate the Token using system default browser click on YES<\/strong>.\n
      8. It will open the URL in the browser there you need to log in and grant the permission.<\/li>\n
      9. After that, you will able to view the access token in the browser. you can copy from there also.\n
      10. At the last, it will again focus on the connection and there it will ask you to save the token if you want to store it. if not then you can manually copy and paste that token.\n
      11. That’s it now click on the Test Connection button to test it.\n
        \"\"<\/a>
        Generate Token and Test Connection<\/p><\/div><\/li>\n<\/ol>\n
        Register OAuth App Private for\u00a0Xero (Private App) OAuth Connection (Depreciated)<\/h3>\n
        Here is how to configure Xero to use Private App (click on below link).<\/p>\n
        https:\/\/developer.xero.com\/documentation\/auth-and-limits\/private-applications<\/a><\/p>\n
        In the above link during these steps<\/a>\u00a0openssl<\/strong> command may throw error about openssl.cnf is missing<\/strong> or cannot load. If you face such error then don’t worry just refer to following workaround.<\/p>\n
        https:\/\/stackoverflow.com\/questions\/14459078\/unable-to-load-config-info-from-usr-local-ssl-openssl-cnf<\/a><\/p>\n
        Basically you can either supply config file path in each command OR set path by issuing SET command before any other command. See below examples (Assuming you installed OpenSSL to c:\\OpenSSL-Win64 folder)<\/p>\n
        c:\\OpenSSL-Win64>openssl req -x509 -config \"C:\\OpenSSL-Win64\\bin\\openssl.cnf\"<\/code><\/pre>\n
        — OR — \u00a0type before command before any other openssl commands<\/p>\n
        c:\\OpenSSL-Win64>set OPENSSL_CONF=c:\\OpenSSL-Win64\\bin\\openssl.cnf\r\n\r\nc:\\OpenSSL-Win64>openssl req -x509 -config \"C:\\OpenSSL-Win64\\bin\\openssl.cnf\"<\/pre>\n
        Once you generate public key, private key and, pfx file we are ready to call Xero API in SSIS<\/strong><\/p>\n
        Configure Xero (Private App) OAuth Connection<\/span><\/h4>\n
        Once you generate certificate using openssl and register Private App for Xero we are ready to move to SSIS piece. Lets see how to do that.<\/p>\n
          \n
        1. Open Visual Studio<\/li>\n
        2. Open existing SSIS Project or create new using File > New > Project > Choose “Integration Services Project” Type under Business Intelligence template category<\/li>\n
        3. Open Package<\/li>\n
        4. Right click inside Connection Managers area and click \u201cNew Connection\u2026\u201d<\/li>\n
        5. From the connection type list select \u201cZS-OAUTH\u201d connection type.\n
        6. On the connection select \u201cXero (Private App)\u201d from Service Provider Dropdown<\/li>\n
        7. Enter Client ID (Obtain this from App created<\/a> in Previous section) App Selection leave \u201cUse Default OAuth App\u201d selected. If you\u00a0created custom OAuth App<\/a>\u00a0then select \u201cUse Custom OAuth App\u201d Option<\/li>\n
        8. Enter some fake value in Client Secret (not needed when you use Private App). Secret will come from certificate file like below.\n
        9. Now go to Certificate tab and Check Use Certificate Enter, Enter certificate location and password like below.\n
        10. Click OK to save OAuth connection manager<\/li>\n<\/ol>\n
          So in the next section<\/strong> from the upper connections, we need to use any of the one connection only<\/strong> for Xero API now let’s try to read data from Xero using\u00a0SSIS JSON Source<\/a>.<\/p>\n
          Xero recently added OAuth 2.0 support so that now recommended way to use as Connection.<\/strong><\/p>\n
          Configure JSON \/ REST API Source – Read data from Xero<\/h3>\n
          Now let’s look at how to configure SSIS JSON Source to read data from Xero API<\/p>\n
            \n
          1. In the control flow ssis toolbox Drag and drop data flow task<\/li>\n
          2. Double click Data flow<\/li>\n
          3. Drag ZS JSON Source (REST API or File) from SSIS Toolbox and drop on Data flow designer<\/li>\n
          4. Double click ZS JSON Source<\/a>\u00a0to configure it.<\/li>\n
          5. Set the following properties\n
              \n
            1. Enter URL\n
              https:\/\/api.xero.com\/api.xro\/2.0\/Invoices<\/pre>\n<\/li>\n
            2. Check Use Credentials and select OAuth connection we created in the previous section<\/li>\n
            3. For Headers grid. Click on Raw Edit<\/strong>\u00a0button and remove enter following (remove default headers)\n
              Accept: application\/json<\/pre>\n<\/li>\n
            4. Click on Select filter and select an array node as below to generate filter expression (e.g. $.Invoices[*] )<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
              \"Configure<\/a>
              Configure SSIS JSON \/REST API Source – Read Xero Invoices<\/p><\/div>\n
              Also, add following header in case of multiple tenants are being used in XERO environment –<\/p>\n
              xero-tenant-id: <TENANT ID FROM XERO><\/pre>\n
              Configure Xero\u00a0REST API Pagination in SSIS JSON Source<\/h3>\n
              By default, Xero may not send you all records and you may have to setup pagination to read more data after the first response.<\/p>\n
              For example, you like to read LineItems from Invoices. For that make sure you use below URL and include page=1 to start with. If you do not include page=1 then Line items are not returned (Yes its ODD but clearly documented here<\/a>).<\/p>\n
                \n
              1. Enter URL as\n
                https:\/\/api.xero.com\/api.xro\/2.0\/Invoices?page=1<\/pre>\n<\/li>\n
              2. Select Filter as $.Invoices[*].LineItems[*]<\/span><\/li>\n
              3. Go to Pagination tab<\/li>\n
              4. Select Pagination by URL Parameter<\/li>\n
              5. Enter page<\/strong> as pagination parameter name like below\n
              6. Click OK to save<\/li>\n<\/ol>\n
                Now when you run package you will see all LineItems for each Invoice will be returned.<\/p>\n
                Loading Xero data to SQL Server<\/h3>\n
                Now last step is loading Xero data to SQL Server. For example purpose we will load data into SQL Server but you can load into any Target (e.g. Flat file, Oracle, Excel) using Microsoft or ZappySys Destination connectors<\/p>\n
                To load Xero data into SQL Server perform following steps<\/p>\n
                  \n
                1. Drag OLEDB destination on Data Flow surface<\/li>\n
                2. Connect ZS JSON Source to Destination<\/li>\n
                3. On OLEDB destination select \/ create new SQL Connection and then Click “New Table”<\/li>\n
                4. Click on Mappings tab and click OK to save<\/li>\n
                5. Execute Package<\/li>\n<\/ol>\n
                  \"Configure<\/a>
                  Configure SSIS OLEDB Destination – Loading Xero Data into SQL Server Table<\/p><\/div>\n
                  \"Xero<\/a>
                  Xero to SQL Server Column Mappings for OLEDB Destination<\/p><\/div>\n
                  \"Loading<\/a>
                  Loading Xero data to SQL Server in SSIS<\/p><\/div>\n
                  POST data to Xero (Insert or Update)<\/h2>\n
                  So far we have seen how to read data from Xero. Now let’s look at how to write data to Xero.<\/p>\n
                  There are two ways you can achieve this.<\/p>\n
                    \n
                  1. Use Web API Destination<\/a> in Data Flow<\/li>\n
                  2. Use REST API Task<\/a> in Control Flow<\/li>\n<\/ol>\n
                    Read this article to learn more about API POST using SSIS<\/a><\/p>\n
                    Using SSIS Web API Task (Write data from SQL Server to Xero)<\/h3>\n
                    \"SSIS<\/h3>\n
                    Using REST API Task to POST data to Xero<\/h3>\n
                    If you have JSON\/XML data already prepared and if you like to POST it to Xero API then REST API Task would be easy to use.<\/p>\n
                    REST API Task Body can be direct string, variable (e.g. {{User::varSomeData}} or Body can come from File\u00a0<\/a><\/p>\n
                    \"SSIS<\/a>
                    SSIS REST API Task – POST data to Xero (Create contacts)<\/p><\/div>\n
                    Read data from Xero Report API<\/h2>\n
                    In previous examples we saw how to read data from various Xero API endpoints but now lets look at how to call Xero Report API<\/a>. For example if you like to extract data displayed in below then you can extract it in Xml or JSON format. Below report is in HTML format but we will show you how to get similar information in JSON format and load into SQL Server Table.<\/p>\n
                    \"Xero
                    Xero Report Example – View Profit and Loss Report in Xero Portal<\/p><\/div>\n
                    To extract above information you can call below API Endpoint (e.g. Profit & Loss Report<\/a>)<\/p>\n
                    GET \r\nhttps:\/\/api.xero.com\/api.xro\/2.0\/Reports\/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28<\/pre>\n
                    You can use Variables to make above URL dynamic in JSON Source as below<\/p>\n
                    https:\/\/api.xero.com\/api.xro\/2.0\/Reports\/ProfitAndLoss?fromDate={{User::StartDate,yyyy-MM-dd}}&toDate={{User::EndDate,yyyy-MM-dd}}<\/pre>\n
                    Each report has its own API URL so make sure you refer to the connect URL for calling different report.<\/p>\n
                    Here is sample response from API<\/p>\n
                    {\r\n  \"Reports\": [\r\n    {\r\n      \"ReportID\": \"ProfitAndLoss\",\r\n      \"ReportName\": \"Profit and Loss\",\r\n      \"ReportType\": \"ProfitAndLoss\",\r\n      \"ReportTitles\": [\r\n        \"Profit & Loss\",\r\n        \"Demo Company (AU)\",\r\n        \"1 February 2018 to 28 February 2018\"\r\n      ],\r\n      \"ReportDate\": \"25 February 2018\",\r\n      \"UpdatedDateUTC\": \"\\\/Date(1519593468971)\\\/\",\r\n      \"Rows\": [\r\n        {\r\n          \"RowType\": \"Header\",\r\n          \"Cells\": [\r\n            { \"Value\": \"\" },\r\n            { \"Value\": \"28 Feb 18\" },\r\n            { \"Value\": \"28 Jan 18\" }\r\n          ]\r\n        },\r\n        {\r\n          \"RowType\": \"Section\",\r\n          \"Title\": \" Income\",\r\n          \"Rows\": [\r\n            {\r\n              \"RowType\": \"Row\",\r\n              \"Cells\": [\r\n                {\r\n                  \"Value\": \"Sales\",\r\n                  \"Attributes\": [\r\n                    {\r\n                      \"Value\": \"e2bacdc6-2006-43c2-a5da-3c0e5f43b452\",\r\n                      \"Id\": \"account\"\r\n                    }\r\n                  ]\r\n                },{\r\n                  \"Value\": \"9220.05\",\r\n                  \"Attributes\": [\r\n                    {\r\n                      \"Value\": \"e2bacdc6-2006-43c2-a5da-3c0e5f43b452\",\r\n                      \"Id\": \"account\"\r\n                    }\r\n                  ]\r\n                },{\r\n                  \"Value\": \"5120.05\",\r\n                  \"Attributes\": [\r\n                    {\r\n                      \"Value\": \"e2bacdc6-2006-43c2-a5da-3c0e5f43b452\",\r\n                      \"Id\": \"account\"\r\n                    }\r\n                  ]\r\n                }\r\n              ]\r\n            },\r\n            {\r\n              \"RowType\": \"SummaryRow\",\r\n              \"Cells\": [\r\n                { \"Value\": \"Total Income\" },\r\n                { \"Value\": \"9220.05\" },\r\n                { \"Value\": \"1250.09\" }\r\n              ]\r\n            }\r\n          ]\r\n        },{\r\n          \"RowType\": \"Section\",\r\n          \"Rows\": [\r\n            {\r\n              \"RowType\": \"Row\",\r\n              \"Cells\": [\r\n                { \"Value\": \"NET PROFIT\" },\r\n                { \"Value\": \"-6250.09\" },\r\n                { \"Value\": \"-7250.09\" }\r\n              ]\r\n            }\r\n          ]\r\n        }\r\n      ]\r\n    }\r\n  ]\r\n}<\/pre>\n
                     <\/p>\n
                    Now lets look at steps to configure JSON Source to extract your Xero Report Data.<\/p>\n
                      \n
                    1. Drag new JSON Source from SSIS Toolbox<\/li>\n
                    2. Double click JSON Source to configure it<\/li>\n
                    3. In the Path \/ Web URL, enter API Report URL to call desired Report Start \/ End Date or any other expected Parameter by report (Refer to API Documentation<\/strong>). For our example we will enter below URL\n
                      https:\/\/api.xero.com\/api.xro\/2.0\/Reports\/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28<\/pre>\n<\/li>\n
                    4. Check Use Credentials<\/strong> and Select Connection we created in the Previous Section<\/li>\n
                    5. In the Filter enter $.Reports[*].Rows[*].Rows[*]<\/span><\/li>\n
                    6. Now go to 2D Array Transform Tab and configure like below. If your UI varies then use Properties window to edit property manually.
                      \nBasically you need to edit
                      \nTransformation Type:<\/strong> 2-dimensional array<\/span>
                      \nColumn Name Filter:<\/strong> $.Reports[0].Rows[*].Cells[*].Value<\/span>
                      \nRow Values Filter:<\/strong> $.Cells[*].Value<\/span><\/li>\n
                    7. Now Click Preview Data. If you get error Column ” does not belong to table nosqldata. Then most likely you using old version with Known Issue (i.e. v2.9.7.10822 or older). Get latest version for fix. For workaround of this issue you must follow next section to specify columns manually. Columns must match count.<\/li>\n<\/ol>\n
                      Here is how it will look like.<\/p>\n
                      \"Configure<\/a>
                      Configure Transform Options for Xero Report API (Dynamic Column Title)<\/p><\/div>\n
                      \"Read<\/a>
                      Read from Xero Report API using JSON Source in SSIS (Dynamic Columns Example)<\/p><\/div>\n
                      Fixing Dynamic Columns Problem in for Xero Report API<\/h3>\n
                      Now let’s look at one common scenario which you have to think. SSIS data flow has fixed metadata so if your Report is generating Dynamic Columns each time based on Date Range then it will be trouble for you because all your columns may come as null if initial date range changes.<\/p>\n
                      To address this issue you have to use Static Column Names as below.<\/p>\n
                      NOTE: You must enter exact same number of columns returned in the Header Row section or more. If you enter less number of columns then you will get error. For example in previous sample report screen we have following 6 Columns. Portal HTML report page shows YTD as extra column but API may not return this column so you will have to count it as 5 columns in below example. Best thing is enter 13 or more columns (typically covers 12 months data). If report doesn’t return all columns then you will get some as NULL but its ok because it will not fail.<\/div><\/div>\n
                      Account (or sometimes blank) | Oct 08 | Sep 08 | Aug 08 | Jul 08 | YTD<\/pre>\n
                      In above case you have to enable following properties. (Below Screenshot has Col1,Col2….. Col13\u00a0 but you can enter less number of columns based on above logic of column count).<\/p>\n
                      NOTE:<\/strong> Use REST API Task<\/a> to obtain Raw JSON and review First Row which contains Number of columns
                      \nFor example if your API return some thing like below then you can use 5 columns<\/strong> (one Blank header + 4 Months<\/strong>)<\/p>\n
                            \"Rows\": [\r\n        {\r\n          \"RowType\": \"Header\",\r\n          \"Cells\": [\r\n            { \"Value\": \"\" },\r\n            { \"Value\": \"28 Feb 18\" }\r\n            { \"Value\": \"28 Mar 18\" }\r\n            { \"Value\": \"28 Apr 18\" }\r\n            { \"Value\": \"28 May 18\" }\r\n          ]\r\n        },<\/pre>\n
                      Once we know how many columns we should expect from API response, we can perform following steps. We will now configure our JSON Source component to use Static Columns.<\/p>\n
                        \n
                      1. Drag new JSON Source from SSIS Toolbox<\/li>\n
                      2. Right click JSON Source > Click on Properties (You can do below using UI too but in old version no option for that – See below this section)<\/li>\n
                      3. Enter following values for property\n
                          \n
                        1. Filter:<\/strong>\u00a0$.Reports[*].Rows[*].Rows[*]<\/span><\/li>\n
                        2. ArrayTransformationType:<\/strong> TransformComplexTwoDimensionalArray<\/span><\/li>\n
                        3. ArrayTransCustomColumns:<\/strong> Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13<\/span>\u00a0 \u00a0\u00a0(Assuming you have 12 months to compare)<\/strong><\/li>\n
                        4. ArrayEnableCustomColumn:<\/strong> True<\/span><\/li>\n
                        5. ArrayTransRowValueFilter:<\/strong> $.Cells[*].Value<\/span><\/li>\n<\/ol>\n<\/li>\n
                        6. Now double click JSON Source to configure<\/li>\n
                        7. In the Path \/ Web URL, enter API Report URL to call desired Report Start \/ End Date or any other expected Parameter by report (Refer to API Documentation<\/strong>). For our example we will enter below URL\n
                          https:\/\/api.xero.com\/api.xro\/2.0\/Reports\/ProfitAndLoss?fromDate=2017-02-01&toDate=2017-02-28<\/pre>\n<\/li>\n
                        8. Check Use Credentials<\/strong> and Select Connection we created in the Previous Section<\/li>\n
                        9. In the Filter enter $.Reports[*].Rows[*].Rows[*]<\/span><\/li>\n
                        10. Now click Preview.<\/li>\n<\/ol>\n
                          For Old Version use Properties Grid<\/strong><\/p>\n
                          \"Configure<\/a>
                          Configure Xero Report Data Extract using SSIS Property Grid (For Older Version)<\/p><\/div>\n
                          For New Version Use UI (It has Specify Columns Manually Option)<\/strong><\/p>\n
                          \"Read<\/a>
                          Read from Xero Report API using parameters in JSON Source in SSIS (Static Columns Example)<\/p><\/div>\n
                           <\/p>\n
                          Common Errors<\/h2>\n
                          Truncation related error<\/h3>\r\n
                          The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected.\u00a0For detailed instructions on how to fix common metadata related errors read an article \"How to handle SSIS errors (truncation, metadata issues)<\/a>\".<\/p>\r\n\r\n
                          Authentication related error<\/h3>\r\nAnother frequent error you may get is an authentication error, which happens when you deploy\/copy a package to another machine and run it there. Check the paragraph below<\/a>\u00a0to see why it happens and how to solve this problem.<\/div>\n
                          Deployment to Production<\/h2>\n
                          In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS<\/a> with your Windows account which you use to create a package.\u00a0So SSIS will fail to decrypt tokens\/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager<\/a>\u00a0or an HTTP Connection Manager<\/a> with credentials, etc.), consider using parameters\/variables to pass tokens\/passwords. In this way, you won\u2019t face authentication related errors when a package is deployed to a production server.<\/p>\r\n
                          Check our article on how to configure packages with sensitive data on your production or development server<\/a>.<\/p><\/div>\n
                          Conclusion.\u00a0What\u2019s next?<\/span><\/h2>\n
                          In this article we have learned how to load data from Xero to SQL Server using SSIS ( drag and drop approach without coding). We used\u00a0SSIS JSON \/ REST API Connector<\/a>\u00a0to extract data from Xero REST API using OAuth. JSON Source makes it super simple to parsing complex \/ large JSON Files or any Web API Response into rows and column so you can load into database like SQL Server.\u00a0Download SSIS PowerPack<\/a>\u00a0to try many other automation scenarios not discussed in this article.<\/p>\n
                          Keywords:<\/strong><\/p>\n
                          Xero Integration with SQL Server | How to extract data from Xero in SSIS? | How to read data from Xero API? | Loading Xero Data into SQL Server. | Xero to SQL Server | SQL Server to Xero | SSIS Xero Integration<\/p>\n
                           <\/p>\n","protected":false},"excerpt":{"rendered":"
                          Introduction Xero is well known Cloud based Accounting Software. In this post you will learn how to implement Xero API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using SSIS in few clicks. We will use SSIS JSON Source to Read data from Xero and Load into SQL Server \/ other […]<\/p>\n","protected":false},"author":1,"featured_media":1621,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[225,17],"tags":[215,60,153,580,223,3,148,174,222,220,221],"yoast_head":"\r\nHow to read\/load data in Xero using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Xero API Integration in SSIS without coding. Learn How to extract \/ load data from Xero to SQL Server Table. Use Xero OAuth Private App (X509 Certificate)\" \/>\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\/reading-loading-data-in-xero-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=\"How to read\/load data in Xero using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Xero API Integration in SSIS without coding. Learn How to extract \/ load data from Xero to SQL Server Table. Use Xero OAuth Private App (X509 Certificate)\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2017-07-25T03:02:24+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-03-31T13:44:42+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"375\" \/>\r\n\t<meta property=\"og:image:height\" content=\"374\" \/>\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:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\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=\"17 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/\",\"name\":\"How to read\/load data in Xero using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png\",\"datePublished\":\"2017-07-25T03:02:24+00:00\",\"dateModified\":\"2025-03-31T13:44:42+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Xero API Integration in SSIS without coding. Learn How to extract \/ load data from Xero to SQL Server Table. Use Xero OAuth Private App (X509 Certificate)\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png\",\"width\":375,\"height\":374},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to read\/load data in Xero 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\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to read\/load data in Xero using SSIS | ZappySys Blog","description":"Xero API Integration in SSIS without coding. Learn How to extract \/ load data from Xero to SQL Server Table. Use Xero OAuth Private App (X509 Certificate)","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\/reading-loading-data-in-xero-sql-server-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to read\/load data in Xero using SSIS | ZappySys Blog","og_description":"Xero API Integration in SSIS without coding. Learn How to extract \/ load data from Xero to SQL Server Table. Use Xero OAuth Private App (X509 Certificate)","og_url":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2017-07-25T03:02:24+00:00","article_modified_time":"2025-03-31T13:44:42+00:00","og_image":[{"width":375,"height":374,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"17 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/","url":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/","name":"How to read\/load data in Xero using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png","datePublished":"2017-07-25T03:02:24+00:00","dateModified":"2025-03-31T13:44:42+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Xero API Integration in SSIS without coding. Learn How to extract \/ load data from Xero to SQL Server Table. Use Xero OAuth Private App (X509 Certificate)","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/xero-integration.png","width":375,"height":374},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to read\/load data in Xero 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\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1602"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=1602"}],"version-history":[{"count":28,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1602\/revisions"}],"predecessor-version":[{"id":11299,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1602\/revisions\/11299"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/1621"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=1602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=1602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=1602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}