SSIS package invoking REST API is failing after 1 minute

0

I have created an SSIS package which is having a ScriptTask to invoke REST API.

Following is C# script task which I have written in SSIS package:

public void Main()
{            
    string serviceHostUrl = Dts.Variables["ParameterTestServiceHostURL"].Value.ToString();            
    string serviceHostApiKey = Dts.Variables["ParameterTestServiceHostApiKey"].Value.ToString();
    string apiName = Dts.Variables["ParameterApiName"].Value.ToString();
    string serviceHostApiEndPoint = String.Format("{0}/json/reply/{1}", serviceHostUrl, apiName);
    string requestData = Dts.Variables["ParameterRequestData"].Value.ToString();
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(serviceHostApiEndPoint);
    request.Method = "POST";
    request.ContentType = "application/json";
    request.ContentLength = requestData.Length;
    request.Headers.Add("x-api-key", serviceHostApiKey);
    request.Timeout = 3600000; //1 hour timeout
    StreamWriter requestWriter = new StreamWriter(request.GetRequestStream(), System.Text.Encoding.ASCII);
    requestWriter.Write(requestData);
    requestWriter.Close();            

    try
    {
        WebResponse webResponse = request.GetResponse();
        Stream webStream = webResponse.GetResponseStream();
        StreamReader responseReader = new StreamReader(webStream);
        string response = responseReader.ReadToEnd();
        Console.Out.WriteLine(response);
        responseReader.Close();
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e)
    {
        Console.Out.WriteLine("-----------------");
        Console.Out.WriteLine(e.Message);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

I have set 1 hour of timeout explicitly, but it is still failing with following error exactly after 1 min of execution, but I can see the service keeps processing until its all operations are completed. That means, service execution is successful, but SSIS package throws exception.

Executed as user: qasql_svc. ----------------- The underlying connection was closed: An unexpected error occurred on a receive. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 8:02:06 AM Error: 2018-10-24 08:03:07.25 Code: 0x00000006 Source: InvokeAPI Description: The script returned a failure result. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:02:06 AM Finished: 8:03:07 AM Elapsed: 60.422 seconds. The package execution failed. The step failed.

When I try to run the same REST API through Postman, I can see success status without any exception, which makes me believe there is no issue with the REST API or its configuration. But something is missing or needs to be corrected at SSIS package or its configuration level.

Can anyone please suggest here?

Thanks

c#
.net
sql-server
ssis
barracuda
asked on Stack Overflow Oct 25, 2018 by Nirman • edited Dec 5, 2018 by Nirman

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0