4

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.

slugster
  • 49,403
  • 14
  • 95
  • 145
HaaroWalo
  • 81
  • 1
  • 9
  • 1
    Walo did you get any solution for the above one..... Since I am also trying to load the data from API to SQL Server tables in VS2017. If you found the solution kindly help me. – venugopal May 23 '22 at 15:12

1 Answers1

2

I just recently had to do this. I initially looked and 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 is the environment we we 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.
Garett
  • 16,632
  • 5
  • 55
  • 63
  • Can you please help me with this issue https://stackoverflow.com/questions/72008106/c-sharp-script-to-load-data-into-sql-server-database-doesnt-work-as-expected/72008750#72008750 – user4912134 Apr 26 '22 at 10:16