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.

    create table

  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.

    script task

  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.

    oledb destination

Solution Worked 0 UpvotesUpvote
        
Solution Didn't Worked 0 DownvotesDownvote


Comments




Search