SSIS package to call REST API and store in SQL tables

Question

I am trying to build an SSIS package that can call a Web API (with or without parameters), get the JSON result, parse and store data from the JSON to SQL tables. Does anyone if this is possible without third party tools such as ZappySys? If so, can you point me to any resources/tutorials/examples as I have not been able to find any.

Answer 1

I just recently had to do this. I initially looked at using third-party libraries, but instead chose to implement a custom Script Task. This task used the .NET HttpClient to call the REST API. There are some caveats to doing this:

• If you use nuget packages in your task, they should be added to the GAC (eg. Json.NET for serialization). on your dev machine as well as production. There are other ways to get around this, but this is the easiest approach. We couldn't do this in the environment we were deploying to, so we used what was already a part of the framework (eg. DataContractJsonSerializer).
• Using async code isn't supported in Script tasks. You will need to wrap your async calls with Task.Run. See this post for details.