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 (26)

  1. dln says:

    Brilliant! Just what I was looking for.

  2. Cboy says:

    Please can you elaboarate more on this approach..and possibly provide working code. I tried this code but it appears not to be working

  3. Oleksandr says:

    Works perfect, thank you!

  4. Sam says:

    Awsome man.. the refenece issue is resolved thanks to ur solution. !!!!

  5. Sam says:

    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

  6. Bala says:

    Excellent!!! It worked for me.

    Thanks

    Bala.

  7. Ananda says:

    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.

  8. katherine says:

    How can i use variable to replace the path, i don't want to hard code the path.

    1. Tony says:

      String path = Dts.Variables[“User::variable_name”].Value.ToString()

      Make sure you include the variable as a readonlyvariable in your script task

  9. Confused says:

    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.

  10. Satya Sandeep says:

    Great solution. Exactly what i am looking for.

  11. Prasanna says:

    Worked exactly well

  12. Siva says:

    How to specify the path from variable..unable to use DTS

  13. Asim says:

    How can I load multiple assemblies togather

  14. Ed says:

    This is excellent, just the thing to resolve the problem I had.

  15. BenJ says:

    I am using to load assemblies from any given location in prod.  To make the path Configurable, you need to make constructor and Event Declaration PUBLIC.  Static declaration prevents you from accessing the Dts object.  Which in turn prevents you seeing any defined package / project variables.

  16. ManoloArbulu says:

    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

  17. Bil says:

    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 !

  18. Menno.H says:

    You saved me from going insane! Thx.

  19. Marc says:

    This worked perfectly. Thanks for the tip.

  20. JayKayOf4 says:

    Quick Hack:

    1. Right click project in script task and click "open folder in file explorer". This should give you the location of the temp directory for the script task visual studio project.

    2. Copy your dlls to the bindebug and binrelease folders.

    3. Reference the dlls as per normal.

    4. When building and deploying, VS will package everything together.

    Hope this helps.

  21. JohnN says:

    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.

  22. JohnN says:

    @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?

  23. Tony says:

    Great post!

  24. Elijah says:

    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.

  25. Howie says:

    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)