SSIS Script Task cannot find assembly reference

Question

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

I use NPOI to read data from Excel and put the NPOI.dll file in the same folder as the SSIS package and added it as a link in the script task. I'm Noob when it comes to NPOI, so I was only messing around at the moment, but still I fell for 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 with the 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, there is a link here and there are no errors.

If I comment on everything except the first line where I declare HSSFWorkBookcalled wb, it works fine.

SSIS script, ?

.

Answer 1

, Script , GAC. .
- AssemblyResolver Script.

Answer 2

AssemblyResolver, Ferdipux. , , "" . , SQL Server 2017 (System.NotSupportedException). LoadFile () UnsafeLoadFrom () . , , , .

, DLL "System.Web.Helpers.dll", "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);
}

...

Answer 3

static ScriptMain()
        {
            AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
        }
        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            //(string)Dts.Variables["User::CustomDLL"].Value;
            if (args.Name.Contains("HtmlAgilityPack"))
            {
                string path = @"C:\Temp\";
                return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "HtmlAgilityPack.dll"));
                //return System.Reflection.Assembly.UnsafeLoadFrom(System.IO.Path.Combine(path, "HtmlAgilityPack.dll"));
            }
            return null;
        }