Run Time Error - at System.RuntimeMethodHandle.InvokeMethod

  • I am getting an error when running an SSIS ETL, converting aN Excel to CSV. Please assist.

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

     

    Csv output is exactly the same as Excel

    Attachments:
    You must be logged in to view attached files.
  • Step-by-step debugging is what I would suggest.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You try to refer to the steps in this article Export SQL Server Table to CSV

  • I found a code that works:

    #region Help: Introduction to the script task
    /* The Script Task allows you to perform virtually any operation that can be accomplished in
    * a .Net application within the context of an Integration Services control flow.
    *
    * Expand the other regions which have "Help" prefixes for examples of specific ways to use
    * Integration Services features within this script task. */#endregion

    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.IO;
    #endregion

    namespace ST_2b47b8ac8e2c4267bda654e8a458e33e
    {
    /// <summary>
    /// ScriptMain is the entry point class of the script. Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
    #region Help: Using Integration Services variables and parameters in a script
    /* To use a variable in this script, first ensure that the variable has been added to
    * either the list contained in the ReadOnlyVariables property or the list contained in
    * the ReadWriteVariables property of this script task, according to whether or not your
    * code needs to write to the variable. To add the variable, save this script, close this instance of
    * Visual Studio, and update the ReadOnlyVariables and
    * ReadWriteVariables properties in the Script Transformation Editor window.
    * To use a parameter in this script, follow the same steps. Parameters are always read-only.
    *
    * Example of reading from a variable:
    * DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
    *
    * Example of writing to a variable:
    * Dts.Variables["User::myStringVariable"].Value = "new value";
    *
    * Example of reading from a package parameter:
    * int batchId = (int) Dts.Variables["$Package::batchId"].Value;
    *
    * Example of reading from a project parameter:
    * int batchId = (int) Dts.Variables["$Project::batchId"].Value;
    *
    * Example of reading from a sensitive project parameter:
    * int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
    */ #endregion

    #region Help: Firing Integration Services events from a script
    /* This script task can fire events for logging purposes.
    *
    * Example of firing an error event:
    * Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
    *
    * Example of firing an information event:
    * Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
    *
    * Example of firing a warning event:
    * Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
    */ #endregion

    #region Help: Using Integration Services connection managers in a script
    /* Some types of connection managers can be used in this script task. See the topic
    * "Working with Connection Managers Programmatically" for details.
    *
    * Example of using an ADO.Net connection manager:
    * object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
    * SqlConnection myADONETConnection = (SqlConnection)rawConnection;
    * //Use the connection in some code here, then release the connection
    * Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
    *
    * Example of using a File connection manager
    * object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
    * string filePath = (string)rawConnection;
    * //Use the connection in some code here, then release the connection
    * Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
    */ #endregion

    /// <summary>
    /// This method is called when this script task executes in the control flow.
    /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    /// To open Help, press F1.
    /// </summary>
    public void Main()
    {
    // TODO: Add your code here
    string ConnString;
    ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
    ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
    var conn = new OleDbConnection(ConnString);
    conn.Open();
    string query;
    query = "SELECT * FROM [" + Dts.Variables["User::Var_sheet_name"].Value.ToString() + "$]";
    var command = new OleDbCommand(query, conn);
    OleDbDataAdapter adap = new OleDbDataAdapter(command);
    var datatable = new DataTable();
    adap.Fill(datatable);

    // Create CSV file
    using (var sw = new StreamWriter(Dts.Variables["User::Var_CsvFilePath"].Value.ToString()))
    {
    for (int row = 0; row < datatable.Rows.Count; row++)
    {
    var strRow = "";

    for (int col = 0; col < datatable.Columns.Count; col++)
    {
    strRow += datatable.Rows[row][col].ToString() + "|";
    }

    // Remove last '|' from row
    strRow = strRow.Remove(strRow.Length - 1);

    // Write row to file
    sw.WriteLine(strRow);
    }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    ///
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion
    }
    }

    My code:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.IO;

    namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
    {
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
    public void Main()
    {
    // TODO: Add your code here
    string ConnString;
    ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
    ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
    var conn = new OleDbConnection(ConnString);
    conn.Open();
    string query;
    query = "SELECT * FROM [" + Dts.Variables["User::Var_sheet_name"].Value.ToString() + "$]";
    var command = new OleDbCommand(query, conn);
    OleDbDataAdapter adap = new OleDbDataAdapter(command);
    var datatable = new DataTable();
    adap.Fill(datatable);

    // Create CSV file
    using (var sw = new StreamWriter(Dts.Variables["User::vr_csv_path"].Value.ToString()))
    {
    for (int row = 0; row < datatable.Rows.Count; row++)
    {
    var strRow = "";

    for (int col = 0; col < datatable.Columns.Count; col++)
    {
    strRow += datatable.Rows[row][col].ToString() + "|";
    }

    // Remove last '|' from row
    strRow = strRow.Remove(strRow.Length - 1);

    // Write row to file
    sw.WriteLine(strRow);
    }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
    }

    enum ScriptResults
    {
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    }
    }
    }

    var

    The SSIS is running very long. The data that is being written is not a lot.  Have i missed something?

    If i modify my Variables to:vr2

    , i am getting this error:

    Error: '2024' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

    • This reply was modified 2 months ago by  yrstruly.
    • This reply was modified 2 months ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply