REST API File Upload using SSIS (Multi Part POST)

Introduction

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

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

Real-world scenarios:

• Uploading Image/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 FREE Trial of SSIS PowerPack (It comes with many REST API Connectors and other 30+ useful tasks/components)

1. Download and install SSIS PowerPack from here
2. From toolbox of SSIS designer drag  ZS SSIS REST API Task
3. Double click 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 its PUT rather POST)
6. Check File Upload option next to Body text box (This is most important so don’t forget 🙂 )

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

First method of uploading file or Posting data to server from local file is RAW upload. To enable this you simply have to make sure 2 things.

1. Check File upload option next to Body
2. In the Body enter local file path starting with @ sign (e.g. @c:\data\customers.csv)

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

Above technique can be used to read POST data content from local file. Using this method only one file can be uploaded. If remote server supports multiple files (i.e. Multi-Part /Form Data POST) then use next method. See below screenshot of file content upload using Raw mode. We used Fiddler to inspect Web request made by REST API Task.

POST file data using SSIS ( Raw file upload)

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

Second method for file upload is Multi-Part Upload. Advantage of using this method is you can POST upload key/value pair along with single or multiple files. To use this method you must make sure API supports it on the other end. Use below steps to POST data using Multi-Part method.  SSIS PowerPack detect RAW mode vs Multi-Part POST based on BODY content. If Body contains Equal sign somewhere and File Upload option is checked then its treated as 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 File upload option next to Body
2. Now click edit button next to Body text box. You may specify files / fields you wish to submit along with your request in the following format. Its key/value pair separated by new line. Each key/value pair has to be separated with equal sign (=).NOTE: Any value starting with at symbol (@) is treated as File Path. If you don’t want that behavior then use slash also known as escape sequence (e.g. Somefield3StartsWithAt=\@MyTwitterHandle). You can do same thing to add new line character in your value. use \r\n for new line. use \t for tabAlso notice name of field can be anything unless your API expects in certain way. Example: Twitter Image Upload API may expect your image file as 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 system automatically detects based on file extension. To exclude content-tye altogether you have to set it as 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 Test button on UI to make sure everything is looking good?

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

Understanding Multipart Upload / File Upload

Now lets take a look how does it look like in Fiddler when actual Multipart POST request is made for REST API File upload. Notice each field included in out HTTP POST request is separated by some random boundary. This boundary is automatically generated by REST API Task. If you post Image then you will see binary data rather than text for file content. Here is some known issue you need to be aware of.

HTTTP Multipart POST – File Upload Example – Check Fiddler Request

Here is a Sample Multi-Part request.

Notice few things

• First line in the body content is Boundary.
• Last line in the body is also Boundary but it has two extra dash at the end after boundary line (i.e. ———–xyzzzzz– )
• Notice name=”something” . This is very important attribute. This is how target server knows which item to extract from your response.
• Content-Type is automatically determined if Multi-Part item is file upload. Based on extension ZappySys automatically determines most common content type. See next section.

Known Issues

Some time you may receive below error if your server is expecting \r\n after 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 content-type for some file extensions. Here is full list of some common extensions and its content-type

Conclusion

If you have need to perform REST API File Upload / REST API integration without learning complex APIs and programming languages (e.g. Python, JAVA, C#) then SSIS PowerPack provides perfect way to do REST API integration with your existing system. You can uncover endless possibilities with drag and drop approach which is not only simple to use but includes many options for complex scenarios too.

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 multi part forms | Rest Client Upload File | C# REST API Upload File | Rest API Upload Image | Rest API Upload File