SSIS Package - Can't find Newtonsoft.Json.dll when deployed to SQL Server

I hope you're fine! So far I never needed to hand in an own question, because I usually found the solution to my problem here at stack overflow. However, this time I'm lost and I hope someone can help me:

I'm just trying to in myself some SSIS-basics. I already programmed a package to download data from a Web-API and to put it into my database. For the JSON response from the API I use the "Newtonsoft.Json.dll" reference. The package itself works fine when executed in Visual Studio, however when I deploy it to the SQL Server and try to execute it from there it fails. I get the following message:

Insert and Update:Error: System.IO.FileNotFoundException: Could not load file or assembly 'Newtonsoft.Json, Version=12.0.0.0, Culture=neutral, PublicKeyToken=a3ce77dd63f8269a' or one of its dependencies. The system cannot find the file specified.
File name: 'Newtonsoft.Json, Version=12.0.0.0, Culture=neutral, PublicKeyToken=a3ce77dd63f8269a' ---> System.IO.FileNotFoundException: The system cannot find the file specified. (Exception from HRESULT: 0x80070002)
   at System.Reflection.RuntimeAssembly.nLoadFile(String path, Evidence evidence)
   at System.Reflection.Assembly.LoadFile(String path)
   at System.AppDomain.OnAssemblyResolveEvent(RuntimeAssembly assembly, String assemblyFullName)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)   

So, I understand that the Newtonsoft.Json.dll cannot be found.

I searched the internet and found that several people had similar problems. I could also find threads here on this site. I understood that I need to do two things:

1. Strong name the Newtonsoft.Json.dll
2. Add it to the GAC

To strong name it, I used the process described here: http://codeingaddiction.blogspot.com/2011/06/how-to-add-strong-name-to-existing-dll_16.html

I also checked whether this worked OK using this: https://blog.codingoutloud.com/2010/03/13/three-ways-to-tell-whether-an-assembly-dl-is-strong-named/

I then installed it to the GAC in the command prompt like this: gacutil -i (Path)Newtonsoft.Json.dll

Unfortunately I still receive the same error message when I try to execute it through SSMS.

Does anyone have another idea? I really appreciate your support!

Christian

Try to add this to your project file:

<MvcRazorExcludeRefAssembliesFromPublish>false</MvcRazorExcludeRefAssembliesFromPublish>

it will be like this:

<PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
    ....
    <AspNetCoreHostingModel>OutOfProcess</AspNetCoreHostingModel>
    <AspNetCoreModuleModel>AspNetCoreModule</AspNetCoreModuleModel>
    <PreserveCompilationContext>true</PreserveCompilationContext>
    ....
    <MvcRazorExcludeRefAssembliesFromPublish>false</MvcRazorExcludeRefAssembliesFromPublish>
    ....
</PropertyGroup>

I guess you are using a .NET Core version that your server doesn't install. with this option, your deployed server will have a folder called refs, which contains all platform related libraries, and works independently with installed version.

If it still happen, the error comes from your code but not environment.