Back Up Zuora data to SQL Server through SSIS

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Zuora ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Zuora.



Easily back up data to SQL Server using the CData ADO.NET Provider for Zuora. This example uses an SSIS workflow to populate a database with Zuora data.

The CData ADO.NET Provider for Zuora connects Zuora data to applications that provide backup, reporting, full-text search, analytics, and more.

This example demonstrates how to use the provider for Zuora inside of a SQL Server SSIS workflow to directly transfer Zuora data into a Microsoft SQL Server database. The exact same procedure outlined below can be used with any CData ADO.NET Data Providers to connect SQL Server directly with remote data via SSIS.

  1. Open Visual Studio and create a new Integration Services project.
  2. Add a new Data Flow task from the toolbox onto the Control Flow screen.
  3. In the Data Flow screen, add an ADO.NET Source and an OLE DB Destination from the toolbox.

  4. Add a new connection and select .NET Providers\CData ADO.NET Provider for Zuora.
  5. In the connection manager, enter the connection details for Zuora data.

    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.

  6. Open the DataReader editor and set the following information:

    • ADO.NET connection manager: In the Connection Managers menu, select the Data Connection you just created.
    • Data access mode: Select 'SQL command'.
    • SQL command text: In the DataReader Source editor, open the Component Properties tab and enter a SELECT command, such as the one below:

      SELECT Id, BillingCity FROM Invoices WHERE BillingState = 'CA'

  7. Close the DataReader editor and drag the arrow below the DataReader Source to connect it to the OLE DB Destination.
  8. Open the OLE DB Destination and enter the following information in the Destination Component Editor.

    • Connection manager: Add a new connection. Enter your server and database information here. In this example, SQLExpress is running on a separate machine.
    • Data access mode: Set your data access mode to "table or view" and select the table or view to populate in your database.
  9. Configure any properties you wish on the Mappings screen.

  10. Close the OLE DB Destination Editor and run the project. After the SSIS task has finished executing, your database will be populated with data obtained from Zuora data.