In this post, we will see how to invoke, from an ETL, the Web API created in the post: Web API with ASP.NET CORE.
We open Visual Studio and we create an Integration Services Project.
The first thing that we will do, it is the creation of a project parameter called urlwebapi, where we
will insert the url of the service:
![](https://www.zoneofdevelopment.com/wp-content/uploads/2019/10/Screenshot-2019-10-04-at-00.51.29-1024x110.png)
Now, we open the file package.dtsx (or the file that you have created in the project) and we create a variable called OutputWebApi, that we will use to save the Web API’s output:
![](https://www.zoneofdevelopment.com/wp-content/uploads/2019/10/Screenshot-2019-10-04-at-00.54.28.png)
Then, we insert a Sequence Container where we will put a Script Task.
![](https://www.zoneofdevelopment.com/wp-content/uploads/2019/10/Screenshot-2019-10-02-at-00.09.11.png)
![](https://www.zoneofdevelopment.com/wp-content/uploads/2019/10/Screenshot-2019-10-04-at-00.58.28-1024x510.png)
Finally, we edit the script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | public void Main() { #region define variables string urlWebApi = Dts.Variables[ "$Project::urlwebapi" ].Value.ToString(); // definition of HttpClient to call the Web API HttpClient clientWebApi = new HttpClient(); // definition of url of the Web API clientWebApi.BaseAddress = new Uri(urlWebApi); #endregion #region define the request header for the service ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls; clientWebApi.DefaultRequestHeaders.Accept.Add( new MediaTypeWithQualityHeaderValue( "application/json" )); #endregion #region call the Web API try { // Call the Web API var responseWebApi = clientWebApi.GetAsync(urlWebApi).Result; if (responseWebApi.IsSuccessStatusCode) { // Read the result var result = responseWebApi.Content.ReadAsStringAsync(); // Save the result in a variable string varResult = result.Result.ToString(); // Save the result in the package's variable Dts.Variables[ "User::OutputWebApi" ].Value = varResult; // Show the result of the Web API MessageBox.Show(Dts.Variables[ "User::OutputWebApi" ].Value.ToString()); // The task has worked without problem Dts.TaskResult = ( int )ScriptResults.Success; } } catch (Exception ex) { Dts.TaskResult = ( int )ScriptResults.Failure; } #endregion } |
We have done and now, we can run the ETL:
![](https://www.zoneofdevelopment.com/wp-content/uploads/2019/10/Capture-1024x504.png)