Automate Zuora Integration Tasks from PowerShell

The CData Cmdlets for Zuora are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Zuora.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Zuora, but also an SQL interface; this tutorial shows how to use both to retrieve Zuora data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Zuora. To access Zuora data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Zuora.

Once you have acquired the necessary connection properties, accessing Zuora data in PowerShell can be enabled in three steps.

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.

• 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:

• 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.

PowerShell

1. Install the module:

   Install-Module ZuoraCmdlets

2. Connect:

   $zuora = Connect-Zuora -OAuthClientID "$OAuthClientID" -OAuthClientSecret "$OAuthClientSecret" -Tenant "$Tenant" -ZuoraService "$ZuoraService"

3. Search for and retrieve data:

   $billingstate = "CA" $invoices = Select-Zuora -Connection $zuora -Table "Invoices" -Where "BillingState = `'$BillingState`'" $invoices

   You can also use the Invoke-Zuora cmdlet to execute SQL commands:

   $invoices = Invoke-Zuora -Connection $zuora -Query 'SELECT * FROM Invoices WHERE BillingState = @BillingState' -Params @{'@BillingState'='CA'}

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Zuora\lib\System.Data.CData.Zuora.dll")

2. Connect to Zuora:

   $conn= New-Object System.Data.CData.Zuora.ZuoraConnection("OAuthClientID=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;Tenant=USProduction;ZuoraService=DataQuery;InitiateOAuth=GETANDREFRESH") $conn.Open()

3. Instantiate the ZuoraDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Id, BillingCity from Invoices" $da= New-Object System.Data.CData.Zuora.ZuoraDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.billingcity }