Learn more
 

Ready to get started?

Download a free trial of the Zuora SSIS Component to get started:

 Download Now

Learn more:

Zuora Icon Zuora SSIS Components

Powerful SSIS Source & Destination Components that allows you to easily connect SQL Server with Zuora through SSIS Workflows.

Use the Zuora Data Flow Components to synchronize with Zuora. Perfect for data synchronization, local back-ups, workflow automation, and more!

In this article

Related articles

How to Import Zuora Data into SQL Server using SSIS


Easily back up Zuora data to SQL Server using the SSIS components for Zuora.

Using SQL Server as a backup for critical business data provides an essential safety net against loss. Backing up data to SQL Server enables business users to more easily connect that data with features like reporting, analytics, and more.

This example demonstrates how to use the CData SSIS Tasks for Zuora inside of a SQL Server SSIS workflow to transfer Zuora data into a Microsoft SQL Server database.

Add the Components

To get started, add a new Zuora source and SQL Server ADO.NET destination to a new data flow task.

Create a New Connection Manager

Follow the steps below to save Zuora connection properties in a connection manager.

  1. In the Connection Manager window, right-click and then click New Connection. The Add SSIS Connection Manager dialog is displayed.
  2. In the Connection Manager type menu, select Zuora. The CData Zuora Connection Manager is displayed.
  3. Configure connection properties.

    Zuora uses the OAuth standard to authenticate users. See the online Help documentation for a full OAuth authentication guide.

    Configuring Tenant property

    In order to create a valid connection with the provider you need to choose one of the Tenant values (USProduction by default) which matches your account configuration. The following is a list with the available options:

    • USProduction: Requests sent to https://rest.zuora.com.
    • USAPISandbox: Requests sent to https://rest.apisandbox.zuora.com"
    • USPerformanceTest: Requests sent to https://rest.pt1.zuora.com"
    • EUProduction: Requests sent to https://rest.eu.zuora.com"
    • EUSandbox: Requests sent to https://rest.sandbox.eu.zuora.com"

    Selecting a Zuora Service

    Two Zuora services are available: Data Query and AQuA API. By default ZuoraService is set to AQuADataExport.

    DataQuery

    The Data Query feature enables you to export data from your Zuora tenant by performing asynchronous, read-only SQL queries. We recommend to use this service for quick lightweight SQL queries.

    Limitations
    • The maximum number of input records per table after filters have been applied: 1,000,000
    • The maximum number of output records: 100,000
    • The maximum number of simultaneous queries submitted for execution per tenant: 5
    • The maximum number of queued queries submitted for execution after reaching the limitation of simultaneous queries per tenant: 10
    • The maximum processing time for each query in hours: 1
    • The maximum size of memory allocated to each query in GB: 2
    • The maximum number of indices when using Index Join, in other words, the maximum number of records being returned by the left table based on the unique value used in the WHERE clause when using Index Join: 20,000

    AQuADataExport

    AQuA API export is designed to export all the records for all the objects ( tables ). AQuA query jobs have the following limitations:

    Limitations
    • If a query in an AQuA job is executed longer than 8 hours, this job will be killed automatically.
    • The killed AQuA job can be retried three times before returned as failed.

Configure the Zuora Source

Follow the steps below to specify the query to be used to extract Zuora data.

  1. Double-click the Zuora source to open the source component editor.
  2. In the Connection Manager menu, select the connection manager previously created.
  3. Specify the query to use for the data extraction. For example: SELECT Id, BillingCity FROM Invoices WHERE BillingState = 'CA'
  4. Close the Zuora Source control and connect it to the ADO.NET Destination.

Configure the SQL Server Destination

Follow the steps below to specify the SQL server table to load the Zuora data into.

  1. Open the ADO.NET Destination and add a New Connection. Enter your server and database information here.
  2. In the Data access mode menu, select "table or view".
  3. In the Table Or View menu, select the table or view to populate.
  4. Configure any properties you wish to on the Mappings screen.

Run the Project

You can now run the project. After the SSIS Task has finished executing, your database will be populated with Zuora data.