Consume Rest Api with SSIS

I try to consume a Rest API (JSON export) with SSIS. Since it seems that a REST source is not supported by SSIS I was looking for a solution but could only find the SSIS JSON Source.

Is there a non proprietary solution or is this feature even supported by the new SQL Server 2016?

I checked that as well could not find anything specific though.

You could use a script component which is supported in 2016 and previous versions.

Make sure that when you add the component you select "source" as the type and not destination or transformation.

Then you add a reference to System.Web.Extensions.dll to the task and write some code to consume the webservice.

This blog post looks like it documents the process nicely, but the key is that you consume the json and override the CreateNewOutputRows method to send whatever you are receiving to the task's output. The output can be connected to any SSIS component accepting a data flow input.

If you insist on using a source component there is JSONSource on codeplex but they don't support SQL 2016 yet, but I don't think it's necessary to add a JSON component if you are only consuming a single service and it's not really complicated to implement yourself.

StackExchange Database Administrators Q#139022, answer score: 5