How to import data from a web service to database by SSIS package

This tutorial is about a solution which calls a web service from a SQL Server Integration Services package, deserialize the JSON result and store the data in the SQL server tables.

Steps

1. Create a table with the columns according to the data received from the JSON response calling the webservice.

   

2. Create a SSIS package and in the Data Flow task, add a Script task. The script task will contain the code for calling the webservice and creating the JSON response.

   

3. In the C# code, call the webservice(SOAP/REST) link and fetch data.
4. Using a foreach loop, deserialize the JSON result and returns the result in a JSON object.
5. Create a OLEDB destination in the SSIS package, and fetch the result from the script task into the SQL database table.