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.

Manmohit Singh 20 Reputation points
2024-07-13T01:33:03.4633333+00:00

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

    }

}

}

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
538 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.
11,006 questions
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 46,376 Reputation points Microsoft Vendor
    2024-07-16T07:38:37.9433333+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.