Loading external assemblies in SSIS script tasks

The standard way of providing SSIS script tasks with access to non-standard assemblies is to install them in the GAC, but if you don't have access/time/whatever, you can load them at runtime in the ScriptMain class's static constructor. A static constructor is called automatically, before the first instance of the class is created or any static members are referenced.

The pattern below includes the following features:

  • a method LoadExternalAssemblies() which defines a ResolveEventHandler; a handler suitable for handling events raised by the CLR when assembly resolution fails. This implementation responds by attempting to load assemblies from C:\Path\To\Assembly\Location; amend this as required to specify your own location(s).
  • a helper method GetAssemblyName(); this serves simply to trim any version and culture suffixes from the assembly name, so that appending .dll will yield the appropriate assembly filename.
  • a static Dictionary field used to collect the names and locations of assemblies loaded by LoadExternalAssemblies().
  • a static constructor which instantiates the Dictionary field and attaches LoadExternalAssemblies() to the AssemblyResolve event (raised when assembly resolution fails).

The pattern also shows the start of the script task's Main() method body (wrapped in a try block) using the ScriptMain object's Dts property to report loaded assemblies and locations in SSIS information events (this is the purpose of the static Dictionary field).

ScriptMain's (non-static) Dts property is unavailable in the static constructor, so clean/ informative error handling can be a challenge here. You may wish to wrap the constructor body in a try/catch block which writes errors to file or elsewhere.

    private static Dictionary<string, string> assemblyLocations;
 
    static ScriptMain()
    {
      assemblyLocations = new Dictionary<string, string>();
      AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(LoadExternalAssemblies);
    }
 
    private static Assembly LoadExternalAssemblies(object sender, ResolveEventArgs args)
    {
      string path = @"C:\Path\To\Assembly\Location";
 
      path = Path.Combine(path, GetAssemblyName(args) + ".dll");
      if (File.Exists(path))
      {
        assemblyLocations.Add(args.Name, path);
        return Assembly.LoadFrom(path);
      }
      return null;
    }
 
    private static string GetAssemblyName(ResolveEventArgs args)
    {
      if (args.Name.IndexOf(",") >= 0)
        return args.Name.Substring(0, args.Name.IndexOf(","));
      return args.Name;
    }
 
    public void Main()
    {
      try
      {
        // *** START OF USER CODE ***
        bool fireAgain = false;
        foreach (string assemblyName in assemblyLocations.Keys)
          Dts.Events.FireInformation(-1, "Main()" , "Loaded " + assemblyName 
            + " from " + assemblyLocations[assemblyName], "", -1, ref fireAgain);
        ...

I don't like this any more than you do, but sometimes you inherit SSIS packages with VB tasks.

   Private Shared assemblyLocations As Dictionary(Of String, String)
 
   Shared Sub New()
 
      assemblyLocations = New Dictionary(Of String, String)
      AddHandler AppDomain.CurrentDomain.AssemblyResolve, AddressOf LoadExternalAssemblies
 
   End Sub
 
   Private Shared Function LoadExternalAssemblies(sender As Object, args As ResolveEventArgs) As Assembly
      Dim path As String = "C:\Path\To\Assembly\Location"
 
      path = IO.Path.Combine(path, GetAssemblyName(args) + ".dll")
      If File.Exists(path) Then
        assemblyLocations.Add(args.Name, path)
        Return Assembly.LoadFrom(path)
      End If
 
      Return Nothing
 
   End Function
 
   Private Shared Function GetAssemblyName(args As ResolveEventArgs) As String
      If args.Name.IndexOf(",") >= 0 Then
         Return args.Name.Substring(0, args.Name.IndexOf(","))
      End If
 
      Return args.Name
   End Function