SSIS Script component: FileNotFoundException: Could not load file or assembly 'MongoDB.Driver

Question

I'm using a script component to upsert data to MongoDB. As MongoDB driver is not signed, and thus can't be added to GAC, I using the following method to load it at runtime from the known location where all needed reference DLL are saved:

private const string AssembyPath = @"C:\Users\acme\source\repos\import-members-and-optins\lib";
    
static ScriptMain()

{
    AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;
}

private static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    // retrieve just a name of this assembly
    var assemblyName = args.Name.Split(',')[0];
    string fullPath = Path.Combine(AssembyPath, string.Format("{0}.dll", assemblyName));
    try
    {
        return Assembly.LoadFile(fullPath);
    }
    catch (Exception ex)
    {
        throw new Exception($"{fullPath} not found", ex);
    }
}

However, I'm getting the following exception, and I'm not even able to debug it, as it's happening before the task is able to run. It's like the handler is never executed. I have checked and my package is running in x86, so I should be able to debug it, but my handler is never hit. :-(

Package Validation Error Error at Data Flow Task [Upsert Mongo [69]]: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'ScriptMain' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'MongoDB.Driver, Version=2.14.1.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified. at ScriptMain..cctor()
--- End of inner exception stack trace --- at ScriptMain..ctor() --- End of inner exception stack trace --- at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)

What I'm missing here?

Answer 1

I think that the main issue is caused by initiating variables globally within the class which is called before the assembly resolver is fired.

Try not to initiate any variable within the public class ScriptMain : UserComponent class and mover them into the appropriate methods or int the PreExecute method.

Try changing your code to the following:

static ScriptMain()

{
    AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;
}

private static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    string AssembyPath = @"C:\Users\acme\source\repos\import-members-and-optins\lib";
    // retrieve just a name of this assembly
    var assemblyName = args.Name.Split(',')[0];
    string fullPath = Path.Combine(AssembyPath, string.Format("{0}.dll", assemblyName));
    try
    {
        return Assembly.LoadFile(fullPath);
    }
    catch (Exception ex)
    {
        throw new Exception($"{fullPath} not found", ex);
    }
}

Also, make sure that no other variables are initiated globally.

Helpful resources

• Assembly not found in SSIS Script Component- Random
• Loading external assemblies in SSIS script tasks
• AssemblyResolve not fired