SSIS Scripts Upgrade causing C# Reflection Issues

0

I have the following class in a library by the name of "EtlUtils.dll".

namespace ETLUtils
{
public class ETLUtils
{
    Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel Dts;

    bool fireagain = true;
    public ETLUtils(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel _Dts)
    {
        this.Dts = _Dts;

        Dts.Events.FireInformation(0, "ETLUtils", "Intiailized ETLUtils", "", 0, ref fireagain);
        var message = String.Empty;

        Variables vars = null;

        Dts.VariableDispenser.LockForRead("System::PackageName");
        Dts.VariableDispenser.LockForRead("System::PackageID");
        Dts.VariableDispenser.LockForRead("System::MachineName");
        Dts.VariableDispenser.GetVariables(ref vars);

        string packageid = (string)vars["System::PackageID"].Value;
        string packageName = (string)vars["System::PackageName"].Value;
        string machineName = (string)vars["System::MachineName"].Value;

        packageid = packageid.Replace("{", "").Replace("}", "");

        Dts.Events.FireInformation(0, "ETLUtils", packageid, "", 0, ref fireagain);
        Dts.Events.FireInformation(0, "ETLUtils", packageName, "", 0, ref fireagain);

    }

    public bool checkFileExists(string filePath)
    {
        bool fileExists = false;

        fileExists = System.IO.File.Exists(filePath);

        var message = String.Format("File [{0}] Exists = [{1}]", filePath, fileExists);

        bool fireagain = true;
        Dts.Events.FireInformation(0, "ETL Utils", message, "", 0, ref fireagain);

        // print another, more prominent message if the file doesn't exist
        if (!fileExists)
        {
            message = String.Format("FILE NOT FOUND [{0}] ", filePath, fileExists);
            Dts.Events.FireInformation(0, "ETL Utils", "------------", "", 0, ref fireagain);
            Dts.Events.FireInformation(0, "ETL Utils", message, "", 0, ref fireagain);
            Dts.Events.FireInformation(0, "ETL Utils", "------------", "", 0, ref fireagain);
        }

        return fileExists;
    }    
}
}

I am able to use C# reflection to instantiate the class and call it's methods an SSIS 2012/C# 2010 script task via Activator.CreateInstance:

public void Main()
{
        string filePath = (string)Dts.Variables["User::Var_FilePath"].Value;
        string etlUtilsFolder = (string)Dts.Variables["$Project::Path_EtlUtils_Folder"].Value;

        if(!etlUtilsFolder.EndsWith(@"\")) {
            etlUtilsFolder += @"\";
        }
        string etlUtilsDll = etlUtilsFolder + "EtlUtils.dll";

        Assembly a = Assembly.LoadFile(etlUtilsDll);

        // call EtlUtils.checkFileExists
        Type t = a.GetType("ETLUtils.ETLUtils");
        MethodInfo m = t.GetMethod("checkFileExists");
        Object o = Activator.CreateInstance(t, new object[] { Dts });

        var _logging = typeof(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel).GetField("_Logging", BindingFlags.NonPublic | BindingFlags.Instance);

        // get the output
        var fileExists = m.Invoke(o, new object[] { filePath });

        // assign output variable
        Dts.Variables["User::Var_File_Exists"].Value = fileExists;

        //////////////////////////////////////////////////


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

When I upgraded this same package/script to SSIS 2017/C# 2012, I get the following error when run against the same library:

System.MissingMethodException
  HResult=0x80131513
  Message=Constructor on type 'ETLUtils.ETLUtils' not found.
  Source=mscorlib
  StackTrace:
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(Type type, Object[] args)
   at ST_97e6e28b269448f8a3880e4261dfdd5f.ScriptMain.Main() in c:\Users\M136815\AppData\Local\Temp\Vsta\6b27fafa76fb4627b1c4b3d64b85a367\ScriptMain.cs:line 107

I also tried using the ConstructorInfo method outlined here with the same error message. Has something changed with respect to reflection in the newer version of c# scripts? It appears that both versions of the script (c# 2010 and 2012) are targeting ".NET Framework 4"

c#
ssis
asked on Stack Overflow Jan 23, 2019 by Michael K

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0