Call REST API through SSIS package

    Consuming REST API in SSIS is not as easy as Consuming Web Service, we have Web service task in SSIS, but there is no specific task for Calling API, hence we will use c# code to consume API inside Script task.

  below are the step by step methods for calling API

1) Use Script task for API calling, declare a variable to hold API URL.

2) Go to script task, there is default c# code. that does not have API references, hence we need to add reference to System.Net.Http.

3)Write a c# code to consume Web API, we can write success or failure logic based on API result.

add below code inside your main() block. use namespace which we added in reference.

using System.Net;

using System.Net.Http;

using System.Net.Http.Headers;

       

public void Main()

{       

            try

            {

                string serviceUrl = Dts.Variables["User::API"].Value.ToString();

                string GUID = Dts.Variables["System::ExecutionInstanceGUID"].Value.ToString();

                

                HttpClient client = new HttpClient( new HttpClientHandler { UseDefaultCredentials = true }) ;

                client.BaseAddress = new Uri(serviceUrl);

                // Add an Accept header for JSON format.    

                client.DefaultRequestHeaders.Accept.Add(

                    new MediaTypeWithQualityHeaderValue("application/json"));

               

                string APIUrl = string.Format(serviceUrl , GUID);

                var response = client.GetAsync(APIUrl).Result;

                if (response.IsSuccessStatusCode)

                {

                    var result = response.Content.ReadAsStringAsync().Result;                    

        

                    Dts.TaskResult = (int)ScriptResults.Success;

                }

                //Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (Exception ex)

            {

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

}

4) we can also use API calling c# code in Script component as Source or Transformation or Destination. also.

 

Thanks !!