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?

score 1 · Answer 1 · answered Nov 20 '19 at 01:32

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.

Nugsson · Answer 2 · 2019-12-02T09:46:26.927

I've achieved this - in VB I'm afraid, but it should be easily translated to C#.

The point that set me on the right track was this, from the article you referenced:

The trick is to realize that .NET's Just-in-Time (JIT) compilation is responsible for loading dependent assemblies, and therefore guarantees that the assemblies referenced in a type's method won't be loaded until just before the method is executed.

My script task therefore looks like this:

' See the Main() task, where this is set
Public Shared referencePath As String

Public Sub New()
    AddHandler AppDomain.CurrentDomain.AssemblyResolve, AddressOf CurrentDomain_AssemblyResolve
End Sub

''' <summary>
''' The Ionic.zip reference isn't in the GAC, so we need to give the assembly resolver a hint as to where it resides:
''' </summary>
''' <param name="sender"></param>
''' <param name="args"></param>
''' <returns></returns>
Private Shared Function CurrentDomain_AssemblyResolve(ByVal sender As Object, ByVal args As ResolveEventArgs) As Reflection.Assembly
    If args.Name.ToLower.Contains("ionic.zip") Then
        Return Reflection.Assembly.LoadFile(Path.Combine(referencePath, "Ionic.zip.dll"))
    End If

    Return Nothing
End Function

Public Sub Main()
    ' Set the static referencePath to our SSIS variable's value - we need to do this because the Ionic.Zip reference,
    ' referred to in the ProcessEmails() method, will otherwise look in the GAC, and won't find what it's looking for.
    referencePath = Dts.Variables("ReferencePath").Value.ToString()

    Dts.TaskResult = ProcessEmails()
End Sub

I haven't included the ProcessEmails() method body, but this will simply do whatever your Main() task does at present.

This way, the assembly resolver doesn't attempt to resolve the 3rd-party assembly until the ProcessEmails() method is called, giving Main() the chance to set it to a Dts.Variable.

Tested and working in a SQL 2017 environment with no 3rd-party DLLs in the GAC.

score 0 · Answer 3 · answered Dec 17 '20 at 15:40

Put the code that needs the dll in an extra method not into Main().
Create a static variable to hold the path to your dll.
In Main() read the project variable or package variable or user variable that contains the path to your dll into the static variable you created.
Call the method you created which holds the call to the dll.

Now the static variable holds the correct value and you can use it in when CurrentDomain_AssemblyResolve is executed. If you use the dll in Main() CurrentDomain_AssemblyResolve is executed before you can read the variable to the static variable and therefore it's empty.

static string path = null;

static ScriptMain()
{
    AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
}

public void Main()
{
    path = (string)Dts.Variables["User::path"].Value;
    MethodsWhichUsesDll();
}

public void MethodsWhichUsesDll()
{
    //Code which uses your dll
}

static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    if (args.Name.Contains("thirdparty"))
    {
        return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "thirdparty.dll"));
    }
    return null;
}

Accessing a Configurable Value in an SSIS Script Task Static Constructor

3 Answers3

Linked