Add third party dll reference in ssis script component

Question

I have added third party reference (Json newtonsoft) dll in my script component (using edit script option), but when i run the package, I am getting an error

Could not load file or assembly 'Newtonsoft.Json, Version=4.5.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The system cannot find the file specified.

Any suggestions?

I will not be able to add the dll in GAC.

I am using SQL Server 2008.

Answer 1

By "Running," I assume running from agent/command-line is failing? It should work from within BIDS/SSDT. The short answer is the DLL must be registered with the GAC or you can download the source code and add that project into the script task and then reference said project.

Looking at the project, it should be a strongly signed DLL (based on presences of Dynamic.snk) and thus capable of being added to the GAC. Oh, but you state you will not be able to add it into the GAC, implying it's a permission not a capability issue.

If that's the case, either compile the project in with the source or surround it with a web service wrapper and then reference the service.

I also saw this answer, seems you can try loading the references dynamically.

• Automated deployment of mixed SSIS / DLL solution

Answer 2

You can using Reflection to load dll at runtime from file system without needing to install in GAC . This is helpful if permission to install in GAC is not availaible .

//Add a Static Constructor which is guaranteed to be called exactly once
// “before the first instance is created or any static members are referenced.”, 
// so therefore before the dependent assemblies are loaded.

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

 //Provide path to dll stored in folder on file system
 static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
 {

     string path = @"D:\DLL\";
     return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "Newtonsoft.dll"));

  }

Ofcourse you need to also Add Reference to dll in script task .