Azure Blob XML File Connector for SSIS : Make generic REST API request (bulk write)

Learn how to make generic REST API request (bulk write) using the Azure Blob XML File Connector for SSIS. This connector enables you to read and write XML files in Azure Blob effortlessly. Streamline, manage, and automate XML files in Blob containers for analytics, reporting, and data pipelines — almost no coding required. We'll walk you through the exact setup.

Let's dive in!

Azure Blob XML File Connector

Powering this integration

High performance
Bidirectional
Low-code / No-code
Easy to use

Built on the ZappySys Native SSIS Connector Framework, the Azure Blob XML File Connector for SSIS is a high-performance, low-code / no-code solution. It enables you to read and write Azure Blob XML File data without any programming right in your SSIS package. Part of the SSIS PowerPack.

Documentation

Prerequisites

Before we begin, make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
SSIS PowerPack is installed (if you are new to SSIS PowerPack, then get started!).

Make generic REST API request in SSIS

Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.

In the new SSIS project screen you will find the following:
- SSIS ToolBox on left side bar
- Solution Explorer and Property Window on right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link.
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
Read the data from the source, using any desired source component. You can even make an API call using the ZappySys JSON/XML/API Source and read data from there. In this example, we will use an OLE DB Source component to read real-time data from a SQL Server database.
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
Select New Connection to create a new connection:

API Destination - Azure Blob XML File

Read and write XML files in Azure Blob effortlessly. Streamline, manage, and automate XML files in Blob containers for analytics, reporting, and data pipelines — almost no coding required.
To configure the Azure Blob XML File connector, choose one of the following methods:
- Choose from Popular Connector List: Select a pre-installed service directly from the dropdown menu.
- Search Online: Use this to find and download a new connector file to your computer.
- Use Saved/Downloaded File: Once the file is downloaded, browse your local drive to load it into the configuration.
After that, just click Continue >>:

Azure Blob XML File
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

Select Generic Table (Bulk Read or Write) table from the dropdown, then select Insert, Update as operation, and hit Preview Data:

API Destination - Azure Blob XML File

Read and write XML files in Azure Blob effortlessly. Streamline, manage, and automate XML files in Blob containers for analytics, reporting, and data pipelines — almost no coding required.

Azure Blob XML File

Generic Table (Bulk Read or Write)

Insert, Update

Required Parameters
Url	Fill-in the parameter...
Request Method	Fill-in the parameter...
Optional Parameters
IsMultiPart
Filter
Request Format (Content-Type)	Default
Body	{$rows$}
JsonOutputFormat	Multicontent
DoNotOutputNullProperty
Batch Size (Default=1)	1
Meta Detection Order	StaticDynamicVirtual
Input Columns - For Mapping (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double
Output Columns (e.g. MyCol1:string(10); MyCol2:int32 ...) - Use bool, int32, int64, datetime, decimal, double
Request Format
Response Format	Default
Headers	Accept: / \|\| Cache-Control: no-cache
Csv - Column Delimiter	,
Csv - Row Delimiter	{NEWLINE}
Csv - Quote Around Value	True
Csv - Always Quote regardless type
Encoding
CharacterSet
Writer DateTime Format
Csv - Has Header Row	True
Xml - ElementsToTreatAsArray
Layout Map	<?xml version="1.0" encoding="utf-8"?> <!-- Example#1: Output all columns --> <settings> <dataset id="root" main="True" readfrominput="True" /> <map src="*" /> </settings> <!-- Example#2: Records under array <?xml version="1.0" encoding="utf-8"?> <settings singledataset="True"> <dataset id="root" main="True" readfrominput="True" /> <map name="MyArray" dataset="root" maptype="DocArray"> <map src="OrderID" name="OrderID" /> <map src="OrderDate" name="OrderDate" /> </map> </settings> --> <!-- Example#3: Records under nested section <?xml version="1.0" encoding="utf-8"?> <settings> <dataset id="dsRoot" main="True" readfrominput="True" /> <map name="NestedSection"> <map src="OrderID" name="OrderID_MyLabel" /> <map src="OrderDate" name="OrderDate_MyLabel" /> </map> </settings> -->

SSIS API Destination - Access table operation

Finally, map the desired columns:

API Destination - Azure Blob XML File

Read and write XML files in Azure Blob effortlessly. Streamline, manage, and automate XML files in Blob containers for analytics, reporting, and data pipelines — almost no coding required.
That's it; we successfully configured the POST API Call. In a few clicks we configured the Azure Blob XML File API call using ZappySys Azure Blob XML File Connector

Conclusion

And there you have it — a complete guide on how to make generic REST API request (bulk write) in SSIS without writing complex code. All of this was powered by Azure Blob XML File Connector, which handled the REST API pagination and authentication for us automatically.

Download the trial now or ping us via chat if you have any questions or are looking for a specific feature (you can also reach out to us by submitting a ticket):

Download Chat

More actions supported by Azure Blob XML File Connector

Got another use case in mind? We've documented the exact setups for a variety of essential Azure Blob XML File operations directly in SSIS, so you can skip the trial and error. Find your next step-by-step guide below: