0

I have created a REST API solution in SSIS script component using C# which fetches the dataset record from Oracle, changes the recordset into JSON format and sends it over to REST Web API Endpoint. Now, when I run the code; it throws the error "Connection forcibly closed by the remote host" on the GetResponse() method. Please see below my code:

#endregion

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.IO;
using System.Net;
using Newtonsoft.Json.Serialization;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;

#endregion

public class ScriptMain : UserComponent
{
    String jsonStr = null;
    int rowNumber = 0;
    private static string _uploadUrl = "https://mycloud.integration.cloud.com:5501/STAGING_PUBLISH/1.0/create";

    private dynamic _sfResult;
    private static string output;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        CreateNewOutputRows();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string jsonStr = @"{""DataIn"": { ""client_id"": ""100001""
                                        , ""first_name"": ""Test""
                                        , ""last_name"": ""User1""
                                        , ""mobile_phone"": ""411111111""
                                        , ""current_status"": ""Yes""},
                            ""ExternalID"" : ""100001"",
                            ""System"" : ""Import"" }";
        //json 
        ProcessJson(jsonStr);
    }

    public void ProcessJson(String VarStr)    {
        if (VarStr.ToString() != "")
        {
            HttpWebRequest httpWebRequest = HttpWebRequest.CreateHttp(_uploadUrl); 
            httpWebRequest.ContentType = "application/json; charset=utf-8";
            httpWebRequest.Method = "POST";
            //var newStream = httpWebRequest.GetRequestStream();

            httpWebRequest.Accept = "application/json; charset=utf-8";
            httpWebRequest.Headers.Add("Authorization", "Basic " + "SVNUU1NJU0NSTUFkbWluc0B1bmlzYS5lZHUuYXU6WEJ1dk5iNVpIbg==");

            using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
            {
                streamWriter.Write(jsonStr);
                body = body + VarStr;
                streamWriter.Flush();
            }

            //httpWebRequest.KeepAlive = false;
            httpWebRequest.ServicePoint.ConnectionLimit = 1;
            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse(); // <<------  FAILS HERE

            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                var responseText = streamReader.ReadToEnd();
                Console.WriteLine(responseText);
            }
        }
    }

    public override void CreateNewOutputRows()
    {
        base.CreateNewOutputRows();

    }
}

I would really appreciate if someone can help me with this. Thanks.

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
dev.babu.2020
  • 1
  • 1
  • What is the error you're getting? When do you get the error? And you get it when you run the package, and not when you compile the script, correct? – digital.aaron Feb 10 '20 at 20:52
  • Have you tried declaring `httpResponse` as an `HttpWebResponse` type? `HttpWebResponse httpResponse = (HttpWebResponse) httpWebRequest.GetResponse();` – digital.aaron Feb 10 '20 at 21:03
  • Check these two questions out: https://stackoverflow.com/questions/28453353/http-post-error-an-existing-connection-was-forcibly-closed-by-the-remote-host/35015311#35015311 & https://stackoverflow.com/questions/21446489/unable-to-read-data-from-the-transport-connection-an-existing-connection-was-fo – digital.aaron Feb 10 '20 at 23:16

0 Answers0