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!
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
-
Open Visual Studio and click Create a new project.
-
Select Integration Services Project. Enter a name and location for your project, then click OK.
-
From the SSIS Toolbox, drag and drop a Data Flow Task onto the Control Flow surface, and double-click it:
-
Make sure you are in the Data Flow Task 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 FileRead 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 / Write) table from the dropdown, then select Insert, Update as operation, and hit Preview Data:
API Destination - Azure Blob XML FileRead 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 FileGeneric Table (Bulk Read / Write)Insert, UpdateRequired 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> --> 
-
Finally, map the desired columns:
API Destination - Azure Blob XML FileRead 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):
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:
