ETL – How to invoke a Web API

By | 07/10/2019

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);

    #region define the request header for the service
    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;

    clientWebApi.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

    #region call the Web API
        // 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

             // The task has worked without problem
             Dts.TaskResult = (int)ScriptResults.Success;
     catch (Exception ex)
          Dts.TaskResult = (int)ScriptResults.Failure;

We have done and now, we can run the ETL:

Leave a Reply

Your email address will not be published. Required fields are marked *