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"
User contributions licensed under CC BY-SA 3.0