SSIS Script Task getting "Exception has been thrown by the target of an invocation." when running with 3rd party dll

Question

I have a process that had been deployed to a production SQL Server SSIS catalog for a couple years, and had been working fine daily. Recently an update was made and deployed. It turns out that source control didn't have the latest working revision (probably my fault!). I reverted to the most recent working version by using the GUI in SSMS and now, whenever I run the package I get "Exception has been thrown by the target of an invocation" at a C# Script Task.

The script task itself doesn't execute, so adding breakpoints inside does nothing. It's SSIS trying to execute the task that is erroring. The script has a reference to BouncyCastle's crypto library. When I comment out all the code that references the library, the script task executes successfully. I can even keep in the using statements.

I have tried removing the reference and re-adding it to no avail.

Answer 1

Setting the version back on the catalog should usually be sufficient, so it sounds like something else might have changed other than the SSIS package. For example if the dll version changed and the appropriate version is not registered in the GAC

If you have a breakpoint in the pre-execute method and its still failing to reach it then there is a good chance that its failing on the BouncyCastle import. Those import generally only works if the dll is registered in the GAC.

Alternatively you can modify the script to manually load the dll from a folder instead of looking for it in the GAC using reflection

You can add the below methods to your script and update the path. Note that this assembly needs to exist in that path on the deployed server. This code will tell the script to load the DLL from that folder on runtime

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

static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    if (args.Name.Contains("BouncyCastle.Crypto"))
    {
        string path = "C:\\SO\\bouncycastle\\";
        return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "BouncyCastle.Crypto.dll"));
    }
    return null;
}