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:
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:
Then, we insert a Sequence Container where we will put a Script Task.
Finally, we edit the script:
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:
