Calling a Restful API in SSIS and returning output

Question

Hi All,

I have a requirement to call a restful API fetch the results. Request will be in xml form and so the response also.

I'm using VS 2019. Please let me know if you have any steps to implement this in c# script task or sample code.

Thanks in advance.

Vikram

Answer 1

Hi @Anonymous ,

 public void Main()      
        {      
            try      
            {      
                string serviceUrl = Dts.Variables["$Project::ServiceUrl"].Value.ToString();      
                HttpClient client = new HttpClient();      
                client.BaseAddress = new Uri(serviceUrl);      
                // Add an Accept header for JSON format.      
                client.DefaultRequestHeaders.Accept.Add(      
                    new MediaTypeWithQualityHeaderValue("application/json"));      
      
                decimal amount = 1200.50m;      
                long tranactionID = 1001;      
                string APIUrl = string.Format(serviceUrl + "/ProcessManager/ProcessData?amount={0}&transactionID={1}", amount, tranactionID);      
                var response = client.GetAsync(APIUrl).Result;      
                if (response.IsSuccessStatusCode)      
                {      
                    var result = response.Content.ReadAsAsync<Response>().Result;      
                    if (result.IsSucess)      
                    {    
                        //TODO update your database based on the result      
                    }      
                    Dts.TaskResult = (int)ScriptResults.Success;      
                }      
            }      
            catch (Exception ex)      
            {      
                Dts.TaskResult = (int)ScriptResults.Failure;      
            }      
        }     

Hope the following links will be helpful:

1.How To Consume Web API Through SSIS Package

2.Download source file from website with SSIS

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 2

Hi Vikram,

I would recommend you check the commercial COZYROC REST technology. You can use any REST API with it.

Answer 3

This code works for me :

public void Main()
{
    // TODO: Add your code here
    ServicePointManager.Expect100Continue = true;
    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
    ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
    string url = "api url";


    try { 
     WebClient c = new WebClient();
    c.Headers.Add("Authorization", "password");
    c.DownloadFileTaskAsync(url, "C:\\temp\\pippo.csv").Wait();
    

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

    catch (Exception ex)
    {
        Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }

}

Answer 4

Hi there,

Absolutely! With ZappySys Rest API Task/Component, you can seamlessly send API requests and efficiently manage both JSON and XML response data.

For comprehensive instructions, I recommend referring to this tutorial:

How to Call REST API in SSIS – Read JSON / XML / CSV

This tutorial provides detailed guidance along with a helpful video tutorial. Feel free to explore it for a deeper understanding.