How to load an Assembly in a SSIS script task that isn't in the GAC

The documented and recommended way to reference a custom assembly from an SSIS Script Task or Script Component is to install it in the Global Assembly Cache (GAC).  However this is not always possible or simple to do.

Here's a simple workaround for loading an assembly from an arbitrary location.  The idea is to register an AppDomain.AssemblyResolve event handler.

The .NET Framework provides the AppDomain.AssemblyResolve event for applications that require greater control over assembly loading. By handling this event, your application can load an assembly into the load context from outside the normal probing paths, select which of several assembly versions to load, emit a dynamic assembly and return it, and so on. This topic provides guidance for handling the AssemblyResolve event.

Resolving Assembly Loads

Which does just what we need.  The question is where and how to wire it up.  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.  This means that in the type's static constructor we can reliably wire up the AssemblyResolve event that will supply the referenced types.

When you create a SSIS Script Task or Script Component SSIS generates a class for you called ScriptMain, and you can wire-up the event in a Static Constructor for that type.  A Static Constructor is guaranteed to be called exactly once "before the first instance is created or any static members are referenced.", so therefore before the dependent assemblies are loaded.

EG:

 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
     static ScriptMain()
     {
         AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
     }
     static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
     {
         if (args.Name.Contains("ssisHelper"))
         {
             string path = @"c:\temp\";
             return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "ssisHelper.dll"));
         }
         return null;
     }
    . . .

You can then drop the referenced assemblies in a well-known location on the SSIS server, or use a package variable to refer to the location and pass that in to the Script Task or Script Component.

David

Comments

• Anonymous
  July 09, 2014
  Brilliant! Just what I was looking for.

• Anonymous
  October 10, 2014
  Please can you elaboarate more on this approach..and possibly provide working code. I tried this code but it appears not to be working

• Anonymous
  October 31, 2014
  Works perfect, thank you!

• Anonymous
  November 30, 2014
  Awsome man.. the refenece issue is resolved thanks to ur solution. !!!!

• Anonymous
  November 30, 2014
  Hey David, i was able to remove the errors showing refernce issues. However i'm still getting the error at the top where i write "using myassembly;" its sayin myassembly no found, are u missing any reference or directive. The code soln which u gave at the top luks similar to this in my solution.. static ScriptMain()        {            AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);        }        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)        {            if (args.Name.Contains("myassembly"))            {                string path = @"C:myprojSSISPackageData";                return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "myassembly.dll"));            }            return null;        } in my ScriptMain.cs file, i'm getting error at the top where we write "using myassembly;" please help ! --Sam

• Anonymous
  December 07, 2014
  Excellent!!! It worked for me. Thanks Bala.

• Anonymous
  December 21, 2014
  Hi , My requirement is: I need to refer the Orcale.dataAccess.dll at the runtime from the configured file location. I tried to Load the Orcale.dataAccess.dll file same approach as above in SSIS package, When I tried to create oracle Client object, the application throws an error message. "could not find the file Oracle.dataaccess.resource" file. Need Help in resolving this issue. Thanks in advance.

• Anonymous
  January 12, 2015
  How can i use variable to replace the path, i don't want to hard code the path.

  • Anonymous
    May 01, 2016
    String path = Dts.Variables["User::variable_name"].Value.ToString()Make sure you include the variable as a readonlyvariable in your script task

• Anonymous
  January 26, 2015
  So how do you make use of it?  How do you reference the assembly, declare the class, instantiate the class, and call a method in the class? I can't do any of this with what you show here, or on the resolving link.

• Anonymous
  April 15, 2015
  Great solution. Exactly what i am looking for.

• Anonymous
  April 23, 2015
  Worked exactly well

• Anonymous
  April 26, 2015
  How to specify the path from variable..unable to use DTS

• Anonymous
  May 24, 2015
  How can I load multiple assemblies togather

• Anonymous
  June 30, 2015
  This is excellent, just the thing to resolve the problem I had.

• Anonymous
  August 10, 2015
  The comment has been removed

• Anonymous
  September 21, 2015
  I'm able to load the assemblies that way but the configuration files of those assemblies are not loaded, how can I load them with the assemblies? Thanks

• Anonymous
  November 22, 2015
  hello david i have the same prolem like Sam , i was able to remove the errors showing refernce issues. However i'm still getting the error at the top where i write "using myassembly;" its sayin myassembly no found, are u missing any reference or directive. The code soln which u gave at the top luks similar to this in my solution.. static ScriptMain()       {           AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);       }       static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)       {           if (args.Name.Contains("myassembly"))           {               string path = @"C:myprojSSISPackageData";               return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "myassembly.dll"));           }           return null;       } in my ScriptMain.cs file, i'm getting error at the top where we write "using myassembly;" please help !

• Anonymous
  December 21, 2015
  You saved me from going insane! Thx.

• Anonymous
  January 05, 2016
  This worked perfectly. Thanks for the tip.

• Anonymous
  January 14, 2016
  The comment has been removed

• Anonymous
  January 25, 2016
  I can't get this to work either.  Could someone who has please post some working code? And there doesn't seem to be a solution to the Dts.Variables problem; if the constructor is Static then Dts is unrecognised, and if the constructor is Public then it's not guaranteed to be called before the first instance is created. As it stands this code seems unworkable in a real situation.

• Anonymous
  January 25, 2016
  @JayKayOf4 Which version of SSIS are you using?  Whenever I close the Script Task and reopen it those references are lost.  I have to add them back each time. And which deployment model are you using - Package or Project?  How are you deploying these extra DLLs to SSISDB?

• Anonymous
  May 01, 2016
  Great post!

• Anonymous
  May 04, 2016
  This definitely got me close but there were a couple errors in the code which needed to be resolved. Here's my corrected code which works properly with project parameters:public ScriptMain(){ AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;}public System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args){ string name = (string)Dts.Variables["$Project::CustomDLLName"].Value; // name of the .dll, example: SSISHelper if (args.Name.Contains(name)) { string path = (string)Dts.Variables["$Project::CustomDLLPath"].Value; // full path including filename.dll return System.Reflection.Assembly.LoadFile(path); } return null;}Note, when you're in the development environment you still need to add the .dll in the References this just makes it so you don't need to add to the GAC and copy to C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn once you deploy to production.

• Anonymous
  May 08, 2016
  Thanks for the article. Anyone who is having issues with getting the dll path from a variable, look at Benj's comment - it was the missing piece of the puzzle for me. Basically you need to remove the "static" qualifier from the constructor and the event handler declarations and just make them public. So: public static ScriptMain() becomes public ScriptMain()and public static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args) becomes public Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)