I created a SSIS package to fetch data from an API to store in the Data Warehouse. But I am getting the runtime error in my script task. I even used try/catch to figure out the error, but nothing is working.
Additionally, I am using a variable 'Token' which I declared in ReadWriteVariables.
The error - at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
The code -
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Http;
namespace ST_048bcb4950ce4ca4a37c6398d9f2a0cd
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string tokenUrl = "HIDDEN";
string username = "HIDDEN";
string password = "HIDDEN";
try
{
using (var client = new System.Net.Http.HttpClient())
{
client.DefaultRequestHeaders.Add("username", username);
client.DefaultRequestHeaders.Add("password", password);
var response = client.GetAsync(tokenUrl).Result;
if (response.IsSuccessStatusCode)
{
var responseBody = response.Content.ReadAsStringAsync().Result;
var Token = ParseTokenFromResponse(responseBody);
Dts.Variables["User::Token"].Value = Token;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Log or handle the exception appropriately
throw new Exception("Error in Script Task: " + ex.Message);
}
}
private string ParseTokenFromResponse(string responseBody)
{
var jsonResponse = Newtonsoft.Json.Linq.JObject.Parse(responseBody);
return jsonResponse["ResultSet"][0]["Value"].ToString();
}
}
}