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

Nirman
  • 6,715
  • 19
  • 72
  • 139
  • It probably failed to connect. I usually recommend using a sniffer like wireshark or fiddler and comparing the working postman with the failing c# application. Usually the issue is a missing header or wrong header in the http. – jdweng Oct 25 '18 at 10:06
  • When I tried to reduce the volume of records (from 1000+ to under 50) the package got executed successfully without any exception. The duration was well within 1 minute. But in real-time scenarios, we expect that REST API to take long to complete execution. (more than 10 minutes). Due to this, it appears to be an issue other than connectivity. – Nirman Oct 25 '18 at 10:16
  • The timeout is in the server, you changed timeout of client. – jdweng Oct 25 '18 at 11:05
  • do you know specifically what line is throwing that error? – Tim Mylott Oct 25 '18 at 19:20
  • Also, could you clarify: Does this error occur when deployed and running from the server or is this error when running from a developer machine or both? "The underlying connection was closed: An unexpected error occurred on a receive." feels like TLS to me. – Tim Mylott Oct 25 '18 at 19:59
  • Not really, TLS changes didn't help. I believe this is something related to Load balance server, as the REST API is hosted in multiple servers, and SSIS is calling NLB URL to access the API. – Nirman Dec 05 '18 at 12:23
  • If this is all your package does then don't do it in SSIS. – Nick.Mc Dec 05 '18 at 13:02

0 Answers0