SSIS Script Task cant find reference to assembly

Here is an example for the custom AssemblyResolver Approach, mentioned by Ferdipux. The given solution was not working with user variables, so you would have to deal with the comments in the documentation to find a "non static" approach. Additionally the posted solutions would not be working anymore when you deploy to a SQL Server 2017 instance and try to read the assemblies from a network share (System.NotSupportedException). Therefore I replaced the LoadFile(path) with the UnsafeLoadFrom(path) call as workaround. Please use it only for your own or other wellknown assemblies, not downloaded assemblies from unknown authors, because this would be a security issue.

Here is the working code, the referenced DLL is "System.Web.Helpers.dll" and the network share path gets configured in the user variable "LibPath" (VS 2015,SQL Server 2017):

public System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    string path = Variables.LibPath.ToString();

    if (args.Name.Contains("System.Web.Helpers"))
    {
        return System.Reflection.Assembly.UnsafeLoadFrom(System.IO.Path.Combine(path, "System.Web.Helpers.dll"));
    }

    return null;
}

/// <summary>
/// This method is called once, before rows begin to be processed in the data flow.
/// </summary>
public override void PreExecute()
{
    base.PreExecute();

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

...


For custom assemblies to be referenced and executed in Script Task, you have to add them to GAC. Here is an article with workflow.
Alternative approach - provide your own AssemblyResolver in Script task code.

Tags:

C#

Dll

Ssis

Npoi