====== 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 [[https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/static-constructors|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 ([[errors_in_script_tasks_and_components|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).

<WRAP center round important 60%>
''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.
</WRAP>

  
<code csharp>    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);
        ...
</code>

===== In VB.NET =====

I don't like this any more than you do, but sometimes you inherit SSIS packages with VB tasks. 
<code vbnet>
   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
</code>

{{tag> ssis script_task assembly}}