Calling API from SSIS and storing the JSON result into the table (Using Execute Sql Task)

 We can call the API in SSIS Package in multiple ways.

·         Using Execute Sql Task

·         Using Script task

We are taking execute sql task.   

Creating the oledb connection and writing the below script.

DECLARE @status int DECLARE @responseText as table(responseText nvarchar(max)) DECLARE @res as Int; DECLARE @url as nvarchar(max) = 'https://jsonplaceholder.typicode.com/posts' EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false' EXEC sp_OAMethod @res, 'send' EXEC sp_OAGetProperty @res, 'status', @status OUT INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText'  IF exists(SELECT  responseText FROM @responseText) BEGIN      DECLARE @json NVARCHAR(MAX) = (SELECT  responseText FROM @responseText)                                    insert into Posts_details                  (                                 userID ,                                 Id ,                                 Post_title ,                                 Post_body                   )      SELECT userId,                                                 id,                                                 title,                                                 body      FROM OPENJSON(@json)           WITH (                  userId NVARCHAR(30) '$.userId',                  id NVARCHAR(50) '$.id',                  title NVARCHAR(50) '$.title',                  body NVARCHAR(50) '$.body'                ); END ELSE BEGIN      DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';      Print @ErroMsg; END EXEC sp_OADestroy @res

  

Click Ok . Now the package is ready to run.

Records in the table.  

Running the package now.

Package executed successfully.

See the records in the table.  

Data loaded successfully.