c# - unable to use variable in SSIS script task -


i have ssis package uses script task dynamically load dll. works when hard-code file path string variable path:

working

namespace st_0a8ce4d87e1c40b4a7bbf7bd3be96cbc.csproj { [system.addin.addin("scriptmain", version = "1.0", publisher = "", description = "")] public partial class scriptmain : microsoft.sqlserver.dts.tasks.scripttask.vstartscriptobjectmodelbase {      #region vsta generated code     enum scriptresults     {         success = microsoft.sqlserver.dts.runtime.dtsexecresult.success,         failure = microsoft.sqlserver.dts.runtime.dtsexecresult.failure     };     #endregion      public scriptmain()     {         appdomain.currentdomain.assemblyresolve += currentdomain_assemblyresolve;     }      public assembly currentdomain_assemblyresolve(object sender, resolveeventargs args)     {         if (args.name.contains("newtonsoft"))         {             string path = @"\\path\to\dll\newtonsoft.json.dll";             return assembly.loadfile(path);         }         return null;     }      public void main()     {         // todo: add code here         dts.taskresult = (int)scriptresults.success;     } } 

}

but when try , replace line:

string path = @"\\path\to\dll\newtonsoft.json.dll";

with:

string path = dts.variables["user::filepath_jsondll"].value.tostring();

where variable value \\path\to\dll\newtonsoft.json.dll, package errors out , says can load file or assembly 'newtonsoft.json'? i'm not sure why work hard-coded value not variable?

update - adding error message

    error: system.reflection.targetinvocationexception: exception has been thrown target of invocation. ---> system.io.fileloadexception: not load file or assembly 'newtonsoft.json, version=8.0.0.0, culture=neutral, publickeytoken=30ad4fe6b2a6aeed' or 1 of dependencies. general exception (exception hresult: 0x80131500)     file name: 'newtonsoft.json, version=8.0.0.0, culture=neutral, publickeytoken=30ad4fe6b2a6aeed' ---> microsoft.sqlserver.dts.runtime.dtsruntimeexception: element cannot found in collection. error happens when try retrieve element collection on container during execution of package , element not there.  ---> system.runtime.interopservices.comexception (0xc0010009): element cannot found in collection. error happens when try retrieve element collection on container during execution of package , element not there.     @ microsoft.sqlserver.dts.runtime.wrapper.idtsvariables100.get_item(object index)    @ microsoft.sqlserver.dts.runtime.variables.get_item(object index)    --- end of inner exception stack trace ---    @ microsoft.sqlserver.dts.runtime.variables.get_item(object index)    @ st_0a8ce4d87e1c40b4a7bbf7bd3be96cbc.csproj.scriptmain.currentdomain_assemblyresolve(object sender, resolveeventargs args)    @ system.appdomain.onassemblyresolveevent(string assemblyfullname)    @ st_6d9e84ae1c284668ac5306f36b3f5f36.csproj.scriptmain.main()      --- end of inner exception stack trace ---    @ system.runtimemethodhandle._invokemethodfast(object target, object[] arguments, signaturestruct& sig, methodattributes methodattributes, runtimetypehandle typeowner)    @ system.reflection.runtimemethodinfo.invoke(object obj, bindingflags invokeattr, binder binder, object[] parameters, cultureinfo culture, boolean skipvisibilitychecks)    @ system.reflection.runtimemethodinfo.invoke(object obj, bindingflags invokeattr, binder binder, object[] parameters, cultureinfo culture)    @ system.runtimetype.invokemember(string name, bindingflags bindingflags, binder binder, object target, object[] providedargs, parametermodifier[] modifiers, cultureinfo culture, string[] namedparams)    @ microsoft.sqlserver.dts.tasks.scripttask.vstataskscriptingengine.executescript() 


Comments