How to download binary file using SSIS REST API Task (image, zip, pdf)

[hardik]

Introduction

In this article, we will guide you through the steps to download and save a file from a REST API JSON response containing binary content using SQL Server Integration Services (SSIS). This process involves using the REST API Task to retrieve the binary content from the JSON response and the Logging Task to save the content to a file.

Prerequisites

Ensure you have the following installed:

1. SQL Server Data Tools (SSDT): Install SQL Server Data Tools, the development tool for SSIS.
2. ZappySys SSIS PowerPack: Download and install the ZappySys SSIS PowerPack.
3. Access to the REST API that returns the binary content in a JSON response.

Steps to Download and Save the File

1. Set Up SSIS Package

   • Create a new SSIS package in SQL Server Data Tools (SSDT).

2. Add REST API Task

   • Drag and drop a REST API Task onto the Control Flow design surface.

3. Configure REST API Task

   • Double-click the REST API Task to configure it.
   • In the Request tab, set up your API request URL, method, and any necessary headers or parameters.
   • In the Response tab, configure the settings to store the binary content in an SSIS variable.

   Example JSON Path for Binary Content:

   • Assume the JSON response looks like this:

     {
       "d": {
         "GetPDF": {
           "BillingDocumentBinary": "<binary-content>"
         }
       }
     }
     

   • Use the JSON path $.d.GetPDF.BillingDocumentBinary to extract the binary content.

4. Create SSIS Variable for Binary Content

   • Create an SSIS variable (e.g., User::BinaryContent) to store the binary content retrieved by the REST API Task.
   • In the Response Settings tab of the REST API Task, map the JSON path to the SSIS variable User::BinaryContent.

5. Add ZS Logging Task

   • Drag and drop a ZS Logging Task onto the Control Flow design surface.

6. Configure ZS Logging Task

   • Double-click the ZS Logging Task to configure it.
   • In the Expression tab, use the FUN_FILE_WRITE_BINARY placeholder function to save the binary content to a file.

   Example Expression:

   <<D:\demo.png|~||~|True,FUN_FILE_WRITE_BINARY>>
   

   • Replace <your-local-folder-path> with the path where you want to save the file (e.g., C:\\Files).

7. Connect the Tasks

   • Connect the REST API Task to the ZS Logging Task using a precedence constraint.

8. Test and Execute the Package

   • Save and execute the SSIS package.
   • Verify that the file is downloaded and saved to the specified local folder.

Sample Screenshots

Below are the sample screenshots of the REST API Task and Logging Task configurations to provide visual guidance.

Conclusion

By following these steps, you can successfully download and save a file from a REST API JSON response with binary content using SSIS. This setup allows you to automate the process of retrieving and saving binary files from API responses, enhancing your data integration workflows.