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.

Answer 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.