SSIS Script Component calling Azure Translator API throws a Buffer Disconnected

MichaelJS 1 Reputation point
2021-04-28T21:17:50.087+00:00

I'm using an SSIS Script component to translate table rows from German into English using Azure Cognitive Services Translator API .

The API returns the translation correctly. But the auto-created file BufferWrapper.cs throws a Dts.Pipeline.BufferDisconnectedException.

The Microsoft documentation for calling the API specifies the main API call method as:

static async Task Main(string[] args)

Meanwhile the SSIS Script component auto-created method for processing each row of a table is defined as:

public override void Input0_ProcessInputRow(Input0Buffer Row)

I think my issue is how to reconcile/integrate these two methods.

public class ScriptMain : UserComponent
{


    private static readonly string subscriptionKey = "###########";
    private static readonly string endpoint = "https://api.cognitive.microsofttranslator.com";
    private static readonly string location = "global";



    public override async void Input0_ProcessInputRow(Input0Buffer Row)
    {
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

        string route = "/translate?api-version=3.0&from=de&to=en";

        // get text and convert to Json
        string textToTranslate = Row.TextToTranslate;
        object[] body = new object[] { new { Text = textToTranslate } };
        var requestBody = JsonConvert.SerializeObject(body);


        using (var client = new HttpClient())
        using (var request = new HttpRequestMessage())
        {
            // Build the request.
            request.Method = HttpMethod.Post;
            request.RequestUri = new Uri(endpoint + route);
            request.Content = new StringContent(requestBody, Encoding.UTF8, "application/json");
            request.Headers.Add("Ocp-Apim-Subscription-Key", subscriptionKey);
            request.Headers.Add("Ocp-Apim-Subscription-Region", location);

            // Send the request and get response.
             HttpResponseMessage response = await client.SendAsync(request).ConfigureAwait(false);

            // Read response as a string.

            string result = await response.Content.ReadAsStringAsync();
            Row.TranslationFromAPI = result;
        }
    }

}
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,191 questions
Azure AI services
Azure AI services
A group of Azure services, SDKs, and APIs designed to make apps more intelligent, engaging, and discoverable.
2,344 questions
{count} votes