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.

Question

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();

    }

}

}

Answer 1

Hi @Manmohit Singh , Welcome to Microsoft Q&A,

The error you are encountering seems to be related to calling methods in the SSIS Script Task. Try the following two points to debug and provide feedback:

Variable Declaration: Make sure the SSIS variables are correctly declared and accessible in the Script Task. Token

Accessing SSIS Variables: When accessing SSIS variables in a Script Task, they must be locked for read/write access before they can be used.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Http;
using Newtonsoft.Json.Linq;

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
            {
                // Ensure the variable is locked for read/write access
                Variables vars = null;
                Dts.VariableDispenser.LockForRead("User::Token");
                Dts.VariableDispenser.GetVariables(ref vars);

                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);

                        // Assign the value to the SSIS variable
                        vars["User::Token"].Value = Token;
                    }
                    else
                    {
                        Dts.TaskResult = (int)ScriptResults.Failure;
                        return;
                    }
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                // Log the full exception details
                Dts.Events.FireError(0, "Script Task Error", ex.ToString(), "", 0);
                throw new Exception("Error in Script Task: " + ex.Message);
            }
        }

        private string ParseTokenFromResponse(string responseBody)
        {
            var jsonResponse = JObject.Parse(responseBody);
            return jsonResponse["ResultSet"][0]["Value"].ToString();
        }
    }
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.