Accessing a Configurable Value in an SSIS Script Task Static Constructor

Question

I have an SSIS package with a script task that needs a 3rd party assembly. Since I am not allowed to place this assembly in the GAC on the SSIS server, I am binding the assembly at run time in the static constructor of the script task. This article is what I used as a guideline. However I want to find a way to avoid hardcoding the path to the assembly file.

My working code looks like this:

  static ScriptMain()
     {
         AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
     }
     static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
     {
         if (args.Name.Contains("thirdparty"))
         {
             string path = @"C:\mydrive\Solution\Reference";
             return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "thirdparty.dll"));
         }
         return null;
     }

What I've tried:

1) Setting the path as a package variable. This doesn't work because the Dts object is not yet instantiated when the static constructor runs so there is no access to the package variables.

2) Tried accessing the app domain that is firing the assembly resolve event like this:

string appDomainPath = ((AppDomain)sender).BaseDirectory;

But this just gets the directory where the VSTA code lives.

I'm out of ideas. Is this even possible?

Answer 1

It is possible to make use of Environment to sneak a package variable into the static constructor:

In an "Initialization" Script Task, Main():

Environment.SetEnvironmentVariable("LIBRARY_PATH", Dts.Variables["$Package::LIBRARY_PATH"].Value.ToString(), EnvironmentVariableTarget.Process);

Then anywhere in future Script Component/Task

        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            string library_path = Environment.GetEnvironmentVariable("LIBRARY_PATH", EnvironmentVariableTarget.Process);
            /* ...
               ... */
        }

Notes:

• Using a package variable is preferred, as it can be overridden based on environment and set via SQL Server Agent:
• In an initial Task, that doesn't need the assembly, we stick our path into the EnvironmentVariable, scoped to the process (to prevent leaking into other concurrent executions) using Main() which has DTS access.
• That's all, now reap the effort... Our library path will be accessible in the static constructor in every other downstream task.