Consume REST API with SSIS in SQL Server 2016

jsonsql serversql-server-2016ssis

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.

Best Answer

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.

Related Solutions

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.

Sql-server – How to determine if a SSIS transform is non-blocking, partially blocking, or fully blocking

An operation which is blocking must wait until all rows have been seen and handled before it can start populating buffers.

An operations which is partially-blocking writes data onto new buffers, which only get handled by the next operation once each buffer (typically just under 10,000 rows) is populated.

An operation which is non-blocking can have the subsequent operation working on the same buffer, as each row is handled.

But many texts will refer to 'partially-blocking' as 'non-blocking', as the behaviour is much closer to 'non-blocking' than 'blocking'.

You should be able to tell what's going on by watching the data flow run, and seeing when the numbers are increasing at each point.

Best Answer

Related Solutions

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

Sql-server – How to determine if a SSIS transform is non-blocking, partially blocking, or fully blocking

Related Question