SSIS Script Component calling Azure Translator API throws a Buffer Disconnected
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;
}
}
}