How to Import / Export data from Zuora API using SSIS

Introduction

In this post, we will learn how to call Zuora API using SSIS.  We will go through steps to read data from Zuora and load into SQL Server using  SSIS REST API Task  and  SSIS JSON Source Connector . At the end of this article, you will find a complete sample (dtsx file).

Tasks/Components in SSIS for Consuming RESTful API / WebService

Below are few components you can use to read/write data using REST API for any web service.

	JSON Source Connector (Read from REST API, JSON File or OData Service): Use this dataflow component when you have to fetch data from REST API webservice like a table. This component allows you to extract JSON data from webservice and de-normalize nested structure so you can save to Relational database such as SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local JSON files or direct JSON string (Wildcard pattern supported too e.g. c:\data\file*.json).
	Web API Destination Connector (POST data to API URL) : Use this dataflow component when you have to load data into target system via API calls (POST to URL). E.g. Loading Contacts into Marketing System like MailChimp for email campaign. Loading documents into CRM or document storage systems e.g. Couchbase or ElasticSearch.
	REST API Task : Use this task when you don’t want to pull REST API data in tabular format but want to call rest API for POST data to server, DELETE data from server or things like download HTML page, extract Authentication tokens etc where you not necessarily dealing data in tabular format. This task also allows you many other options such as saving RAW response into variable or file.
	XML Source Connector (SOAP, File, REST) : Use this dataflow component when you have to fetch data from XML or SOAP webservice and consume data like a table. This component allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local XML files or direct XML string.

Screenshot of Complete SSIS Package (Zuora to SQL Server)

Here is screenshot of our full process (we going to outline in next sections)

SSIS Zuora API Integration – Read/Write Data (Load Zuora Data to SQL Server Table)

Step-By-Step : Using SSIS JSON Source / REST API Task to read Zuora API 

Zuora API is somewhat different than other APIs we documented here because Zuora API uses very different Pagination Technique. Some common pagination techniques listed here but it’s not used by Zuora API so we have to do few extra steps.

Let’s look at step by step.

Configure Zuora OAuth Connection

The first step to connect to Zuora API is to configure  the OAuth App (Ask your Zuora Admin or Follow Steps listed here). Once you create Oauth App get ClientId and ClientSecret.

Now let’s create new SSIS

1. Right click in the Connection Manager Panel > Click New Connection…
2. Select ZS-OAUTH from the list
3. Configure OAuth Connection as below
   1. Set OAuth Version OAuth2
   2. Set OAuth Grant Type to Client Credentials Grant
   3. Enter Client ID and Client Secret
   4. For Token URL enter URL https://rest.zuora.com/oauth/token
   5. Goto Advanced Tab. Select SSL/TLS Protocol v3.0 or Higher
4. Click Test (Confirm Green Checkmark)
5. Click OK to Save

SSIS OAuth Connection For Zuora API

Configure Variables

Now lets configure few SSIS variables.

SSIS Variables for Zuora API Integration

Configure API Pagination

Now let’s configure For Loop such a way that it keeps looping until @continue flag is True

1. Drag For Loop Task from SSIS Toolbox
2. Configure as below
   1. Enter EvalExpression as @continue==true
   2. Enter AssignExpression as @counter=@counter+1

Configure API Pagination using For Loop Task

Configure API Call using SSIS REST API Task

Now let’s configure REST API Task to make a call to get data from Zuora API and save JSON response into File (which we will parse in next step)

So if we making the first request will call API via https://rest.zuora.com/v1/action/query   and any call after that (2nd response or Higher) we will call https://rest.zuora.com/v1/action/queryMore . This API uses SQL like query language ZOQL. Click here to learn more about ZOQL

This is controlled by Expression we defined in URL variable.

Configure SSIS REST API Task – Call Zuora Query API (ZOQL – SQL Query)

Configure SSIS REST API Response Settings – Save Zuora Query to JSON File

Configure Data Flow (Read Zuora Data and Load into SQL Server Table)

We will skip this configuration on how to read JSON file and Save to SQL Server. Because it will be covered in next section.

Inside Data Flow when you configure ZS JSON Source

Make sure to set Filter to $.records[*]

Parse Pagination Attributes (done flag, queryLocator for next page)

Once that done you can parse the response from saved File. We need to check for 2 attributes (done and queryLocator ). Parse JSON as below.

SSIS JSON Parser Task –
Parse Multiple Fields from JSON FIle and Save to Variable

Detect Last Page (Set Variables)

Drag two Set Expression Tasks from SSIS Toolbox.

For First Task [Set Continue Flag] Enter Expression as  @continue=!@doneFlag

For First Task [Set Query Locator ] Enter Expression as  @[User::bodyString]="{ \"queryLocator\" : \"" + @[User::queryLocator] + "\" }"

Save and execute Package

That’s all now you can save package and execute. Check Progress.

Debug Web API call using Fiddler

To test how things looking behind the scene we strongly suggest to use tool like  fiddler. You can double click the URL entry (Right side) to see Request and Response Panels. Top panel is Request (URL, Headers, Body) and Bottom Panel is Response.

Debugging Web API call using Fiddler in SSIS

Loading data from REST API to SQL Server

Once you setup HTTP / OAUTH connection we can use JSON Source or XML Source to extract data from API and  load into SQL Server. If you are API is XML based API then use XML Source in below examples else use JSON Source. 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 To load REST API data into SQL Server perform the following steps (Screenshots are generic and used for demonstration for purpose, your values can be different than below)

1. Go to SSIS Data Flow tab
2. Drag ZS JSON Source from the SSIS Toolbox (Use ZS XML Source if your API is XML based API)
3. Double click to edit Source
4. Set API URL you like to call, Check Use Credentials if you need to use HTTP or OAuth connection
5. Set Method / Body if its other than GET. For method other than GET make sure you select correct Content-Type from Dropdown (e.g. application/json). This indicate input data format in Body.
6. Set HTTP Headers if needed (in Most cases you won't need to set this)
7. Select Filter (many times this is not needed). If your API response has array node then select it here. For example if your response looks like this { rows: [ {...}, {...}, {...} ] } then filter would be $.rows[*]. Select Array node you like to extract in the Filter selection UI.
8. Configure Pagination if needed on the pagination tab. Check your API documentation see it supports pagination.
9. Click OK to Save component settings and generate desired metadata / columns for the output.
10. Now drag OLEDB destination from the SSIS toolbox and drop on the Data Flow surface (technically you can use any valid Destination but for demo we will use OLEDB Destination)
11. Connect Source to Destination Component
12. On OLEDB destination select / create new SQL Connection and then Click "New Table"
13. Click on Mappings tab to map columns by names and click OK to save
14. Execute the Package

Configure SSIS OLEDB Destination – Loading REST API Data into SQL Server Table

REST API to SQL Server Column Mappings for OLEDB Destination

Loading REST API to SQL Server in SSIS

POST / Write Data to API (Insert, Update or Delete)

There will be a time you like to automate POST actions (e.g. create new incident via API call). Check this article to learn more on how to POST , DELETE, PUT data using API calls

Common Errors

There are many error can occur during runtime. Here are most common errors you may face at runtime.

Truncation related error

The most common error you may face when you run a 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. For detailed instructions how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is authentication error, which happens when you deploy/copy a package to another machine and run it there. Check Deployment to Production below to see why it happens and how to solve this problem.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So 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 a SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way you won’t face authentication related errors when package is deployed to a production server. Check our article on how to configure packages with sensitive data on your production or development server.

Download Sample SSIS Package (2012 or Higher Version)

Click here to download SSIS Sample Package – ZuoraApiPagination.dtsx

Conclusion. What’s next?

In this article, we have learned how to load data from Zuora and load into SQL Server using SSIS ( drag and drop approach without coding). We used SSIS JSON / REST API Connector to extract data from Zuora REST API using OAuth. JSON Source Connector makes it super simple to parsing complex / large JSON Files or any Web API Response into rows and column so you can load into a database like SQL Server. Download SSIS PowerPack to try many other automation scenarios not discussed in this article.