REST API File Upload using SSIS (Multi Part POST)

Introduction

In this post, you will learn how to use the SSIS REST API Task to perform REST API File Upload (i.e., RESTful file upload) to a web server. Typically, to access the REST API, you need to use a programming language, but in this post, we will use a drag-and-drop visual approach.

File upload using HTTP/HTTPS protocol, sometimes also known as multipart/form-data POST. Many REST APIs support file upload using HTTP methods such as POST or PUT.

Real-world scenarios:

• Uploading images/media to Twitter or Facebook
• Import Marketo Lead files using REST API

How to do REST API File Upload using SSIS

First, you will have to download a FREE Trial of SSIS PowerPack (It comes with many REST API Connectors and over 30+ functional tasks/components)

1. Download and install SSIS PowerPack from here
2. From the toolbox of the SSIS designer, drag the ZS SSIS REST API Task
3. Double-click the REST API Task to configure it
4. Specify URL where you wish to POST file/other data
   Example: http://myserver/api/v1/uploaddata
5. Select HTTP Request Method as POST ( make sure your API Documentation for this. Sometimes it’s PUT rather than  POST)
6. Check the File Upload option next to the Body text box (This is most important so don’t forget 🙂 )

Method 1 – Upload file using Raw Mode (Or POST data from a local file)

The first method of uploading a file or posting data to the server from a local file is a RAW upload. To enable this you simply have to make sure 2 things.

1. Check the File upload option next to Body.
2. In the Body, enter the local file path starting with the @ sign (e.g., @D:\Zappysys\CSV files\example1.csv)

When you don’t include a Key/Value pair and only the filepath is found, then the request is treated as a RAW Upload. In raw uploads, the default content type is application/octet-stream. If you want to put a custom Content-Type, then edit the Headers in the Grid.

The above technique can be used to read POST data content from a local file. Using this method, only one file can be uploaded. If the remote server supports multiple files (i.e., Multi-Part/Form-Data POST), use the following process. See the screenshot below of the file content upload using Raw mode. We used Fiddler to inspect the REST API Task’s Web requests.

POST file data using SSIS ( Raw file upload)

Method 2 – Upload file(s) using Multi-Part / Form Data mode (Mixed content, multiple files)

The second method for file upload is Multi-Part Upload. The advantage of using this method is that you can POST upload a key/value pair along with single or multiple files. To use this method, you must make sure the API supports it on the other end. Use the steps below to POST data using the Multi-Part method.  SSIS PowerPack detects RAW mode vs Multi-Part POST based on BODY content. If the Body contains an Equal sign somewhere and the File Upload option is checked, then it’s treated as a Multi-Part upload. If File Upload Option is checked and Body contains only file path which starts with @ sign then its treated as Raw content upload.

1. Check the File upload option next to Body.
2. Now, click the edit button next to the Body text box. You may specify files/fields you wish to submit along with your request in the following format. It’s a key/value pair separated by a new line. An equal sign (=) must separate each key/value pair.NOTE: Any value starting with an at symbol (@) is treated as a File Path. If you don’t want that behavior, then use a slash, also known as an escape sequence (e.g., Somefield3StartsWithAt=\@MyTwitterHandle). You can do the same thing to add a new line character in your value. Use \r\n for a new line. Use \t for tab. Also, notice that the field name can be anything unless your API expects it in a specific way. Example: Twitter Image Upload API may expect your image file as a media field. In that case you would write ….  media=@c:\data\someimage.png …. rather than File1=@c:\data\someimage.png
   
   File1=@c:\data\somedata.txt File1.Content-Type=text/plain File2=@c:\data\someimage.png File2.Content-Type=image/png Somefield1=abcdef Somefield2=123456 Extrafield3WithNewLine=This is line1\r\nThis is line2 with tab\t and more \t tab Somefield3StartsWithAt=\@MyTwitterHandle

   1 2 3 4 5 6 7 8

   File1=@c:\data\somedata.txt File1.Content-Type=text/plain File2=@c:\data\someimage.png File2.Content-Type=image/png Somefield1=abcdef Somefield2=123456 Extrafield3WithNewLine=This is line1\r\nThis is line2 with tab\t and more \t tab Somefield3StartsWithAt=\@MyTwitterHandle

   Click OK to Save above.
   NOTE: If you do not specify Content-Type of file using SomeFieldName.Content-Type attribute, then the system automatically detects based on the file extension. To exclude content-type altogether, you have to set it as a blank string, like below
   

   file1=@c:\temp\dump.json file1.Content-Type=

   1 2	file1=@c:\temp\dump.json file1.Content-Type=

3. Now, click the Test button in the UI to make sure everything looks good.
   

   Uploading files using REST API in SSIS ( REST API File upload using Multipart form-data POST)

Understanding Multipart Upload / File Upload

Now, let’s take a look at what it looks like in Fiddler when an actual Multipart POST request is made for the REST API File upload. Notice that each field included in our HTTP POST request is separated by some random boundary. The REST API Task automatically generates this boundary. If you post an Image, then you will see binary data rather than text for file content. Here are some known issues you need to be aware of.

HTTP Multipart POST – File Upload Example – Check Fiddler Request

Here is a Sample Multi-Part request.

Notice a few things

• The first line of the body content is “Boundary.”
• Last line in the body is also a Boundary, but it has two extra dashes at the end after the boundary line (i.e. ———–xyzzzzz– )
• Notice name=”something”. This is an essential attribute. This is how the target server knows which item to extract from your response.
• Content-Type is automatically determined for a Multi-Part item when it is a file upload. Based on extension, ZappySys automatically selects the most common content type. See the next section.

Known Issues

Sometimes you may receive the following error if your server is expecting \r\n after the last Multi-Part boundary. Here is the fix for this error.

Most common extension and Content-Type

In the multi-part upload, you might have noticed that ZappySys adds a content type for some file extensions. Here is a full list of some common extensions and their content-type

Conclusion

If you need to perform REST API file upload or REST API integration without learning complex APIs or programming languages (e.g., Python, Java, C#), SSIS PowerPack provides a perfect way to integrate REST APIs with your existing system. You can uncover endless possibilities with a drag-and-drop approach that is not only simple to use but also offers many options for complex scenarios.

Keywords

How to perform rest api file upload | How to do RESTful file upload in C# | How to do multipart file upload without cURL | How to upload multiple files using HTTP multipart forms | Rest Client Upload File | C# REST API Upload File | Rest API Upload Image | Rest API Upload File