How to batch REST API requests in SSIS (Bulk Operation)

Introduction

In our previous article we saw how to POST data to REST API using few different ways. Now let’s go one step further and discuss another common scenario to batch REST API requests in SSIS. For efficient data transfer many API provides you to submit multiple records in a single request. In this article we will look at few ways to batch our requests using SSIS JSON Generator Transform and  SSIS Web API destination.

Method 1 – Batch records using SSIS JSON / XML Generator Transform

First let’s look at common way to group multiple records and generate single JSON or XML document for desired batch size. We will also see another technique where you can assign Unique BatchID to each document.

For example below screenshot shows how to product one JSON document for every 10 input rows. We have also use BatchID column on the root level. This will use use BatchID from first row of that batch. You can use XML Generator Transform if you like to produce XML Document rather than JSON.

To generate Unique BatchID you can use function like newid() in SQL Server.

Using SSIS JSON Generator Transform with Batch Option (Multiple Records in a single document)

Here if full flow to batch your JSON Requests and Submit to REST API.

POST data to REST API in Bulk Mode (Batch multiple rows)

Method 2 – Using SSIS Web API destination Batch Mode with Header / Footer

Another method of batching is use Web API destination and turn on Batch mode.  You can use Fiddler to debug your web requests incase you are not sure how its generating and sending POST requests after this setting.

Configure SSIS Web API Destination for Bulk API call (Insert multiple rows)

Method 3 – Batch CSV records using CSV Generator Transform

So far we looked at how to batch JSON and XML format. Now let’s look at scenario where you have to POST CSV formatted data to REST API endpoint.

Assume that you have an API endpoint where you can submit data in multiple batches. Also you have few additional requirements

1. First row of First batch must have column names (any other batch must not contains CSV Header)
2. In each Batch we want to submit maximum 5 rows as per API call
3. Each batch must contain Unique Batch Number in URL

Here is high level flow.

Generate CSV using Batch Setting , Send data to REST API in multiple batches, Debug REST API in Fiddler

Include CSV Header in First Batch Only

Now let’s break down the problem and find out how to do. First problem is we need to include CSV Header only in First batch. To achieve this we use old school trick where we output column names as data row… and when we generate CSV in the next section we will turn off Column Headers option.

If you are consuming records from SQL Server or any other RDBMS you can write following style source query. If you don’t use relational source then use native UNION Transform in SSIS.

Batch records and output CSV data

Now next step would be to generate CSV data but in batches… so in our example we have to output size of 5 rows per batch. For this perform the following steps

1. Drag ZS CSV Generator Transform (Included in v2.8 and Higher)
2. Connect source to CSV Generator Transform
3. Now before we edit in UI mode, right click and select Properties. Change following 3 properties  (You can also change QuoteAroundValue and Dimiliter if needed). In future version batch setting will be visible in UI mode too.
   1. ArrayBatchSize  = yourbatchsize (in our case it was just 5 rows per batch)
   2. OutputMode = SingleFileFormat
   3. FirstRowsHasColumnNames  =False
4. Now double click to configure ZS Generator Output
5. Right click on Mappings folder node and Add Elements > Select Multiple Columns  > Check columns you like to output
6. Click OK to Save UI

Generate CSV data in SSIS (Batch Output Setting)

Generate unique URL with Batch using Script Component

Now next task we have to achieve is generate Unique Batch Number. Put that Batch ID in URL like below…

1. For this drag SSIS Script Component and select Transformation Mode
2. Now go to Inputs and Outputs tab > Select Output Columns > Click Add Column > Name it as URL > Configure DataType as DT_WSTR , Length = 500
3. Now got Script Tab and Click Edit. Remove old code and enter following code (Assuming You selected C#). This code will generate new URL with Unique batch ID each time. You can use this same technique to generate Unique row number too (in that case rather than DT_WSTR type you can use DT_I4 type)
   C#

   [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { private static int _batch; public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.Url = string.Format("http://httpbin.org/post?batch={0}",_batch); //If you have to send GUID rather than Integer for Batch ID then use below code //Row.Url = string.Format("http://httpbin.org/post?batch={0}",System.Guid.NewGuid()); _batch = _batch+1; } }

   1 2 3 4 5 6 7 8 9 10 11 12 13

   [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent {     private static int _batch;     public override void Input0_ProcessInputRow(Input0Buffer Row)     {         Row.Url = string.Format("http://httpbin.org/post?batch={0}",_batch);         //If you have to send GUID rather than Integer for Batch ID then use below code         //Row.Url = string.Format("http://httpbin.org/post?batch={0}",System.Guid.NewGuid());           _batch = _batch+1;     } }

4. Click OK to Save script.

Output Unique URL or Number using SSIS Script Transform

Configure Web API Destination to POST CSV Data

Now final step is to configure SSIS Web API Destination to POST CSV data to API.

1. Drag ZS Web API Destination and connect Source Transform to Web API destination.
2. Double click to configure
3. Select Connection Manager
4. Once connection setup, select Input Column for URL ( This will be same column from Script Transform)
5. Select column for Body (e.g. ZS_CSV_OUT)
6. Click OK to save

POST data to REST API in multiple batches using SSIS Web API destination

Debug Web API Requests using Fiddler

Now we ready to run entire flow. Once you execute data flow, observe Input rows before CSV Generator Transform and after. CSV Generator transform must produce less rows before we now grouping multiple rows in a single batch.

Another important aspect to play with API request is to debug it, We recommend you to to look at this article about using Fiddler to debug web requests.

Download Sample Package

Here is the Sample SSIS Package (2012 format) – Rest API_Batching

Conclusion

In this post, we saw how easy it is to handle complex REST API  / CSV /JSON / XML operations in SSIS. We configured REST API POST Operation using Batching Method. Hope this post helps you to achieve your API integration without coding.  Feel free to download SSIS PowerPack and try many other components not discussed in this article.