SSIS Script Task cant find reference to assembly

Question

I have an SSIS package that uses a script task to populate a datatable with data from a variety of different file types including excel.

I am using NPOI to read in the data from Excel and have put the NPOI.dll file in the same folder as the SSIS package and added it as a reference in the script task. I'm a Noob when it comes to NPOI so I'm only tinkering at the moment, but even still I have fallen at the first hurdle!

My script contains the code below (which i copied from this SA answer):

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

HSSFWorkbook wb;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
    wb = new HSSFWorkbook(file);
}

but fails with the following error message: Could not load file or assembly 'NPOI, Version=2.1.1.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1' or one of its dependencies. The system cannot find the file specified

But when I go into the script task, the reference is there and there are no errors.

If I comment out everything except the first line where I declare a HSSFWorkBook called wb it runs fine.

Have I added the references incorrectly or is adding references to a SSIS script task notoriously difficult?

As always any help is greatly appreciated.

Solution 1

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.

Solution 2

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);
}

...