Could not load file or assembly 'Newtonsoft.Json, Version=6.0.0.0 in SSIS 2016

Question

I am trying to use newtonsoft.Json in SSIS C# component as a source. I am getting the below error.

System.IO.FileNotFoundException: Could not load file or assembly 'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The system cannot find the file specified. File name: 'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'

SSDT version: Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22

If I use visual stuido 2017 and newtonsoft V9..package is working fine. But unfortunately, server is lower version , we have to go with VS2016 only. Any help on this issue is much appreciated, have been struggling more than 10 days.

region Namespaces
using System;
using System.Collections.Generic;
using System.Data;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Security.Policy;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;


#endregion

/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    #region Help:  Using Integration Services variables and parameters
    /* To use a variable in this script, first ensure that the variable has been added to
     * either the list contained in the ReadOnlyVariables property or the list contained in
     * the ReadWriteVariables property of this script component, according to whether or not your
     * code needs to write into the variable.  To do so, save this script, close this instance of
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
     * Script Transformation Editor window.
     * To use a parameter in this script, follow the same steps. Parameters are always read-only.
     *
     * Example of reading from a variable or parameter:
     *  DateTime startTime = Variables.MyStartTime;
     *
     * Example of writing to a variable:
     *  Variables.myStringVariable = "new value";
     */
    #endregion

    #region Help:  Using Integration Services Connnection Managers
    /* Some types of connection managers can be used in this script component.  See the help topic
     * "Working with Connection Managers Programatically" for details.
     *
     * To use a connection manager in this script, first ensure that the connection manager has
     * been added to either the list of connection managers on the Connection Managers page of the
     * script component editor.  To add the connection manager, save this script, close this instance of
     * Visual Studio, and add the Connection Manager to the list.
     *
     * If the component needs to hold a connection open while processing rows, override the
     * AcquireConnections and ReleaseConnections methods.
     * 
     * Example of using an ADO.Net connection manager to acquire a SqlConnection:
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
     *
     * Example of using a File connection manager to acquire a file path:
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
     *  string filePath = (string)rawConnection;
     *
     * Example of releasing a connection manager:
     *  Connections.SalesDB.ReleaseConnection(rawConnection);
     */
    #endregion

    #region Help:  Firing Integration Services Events
    /* This script component can fire events.
     *
     * Example of firing an error event:
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
     *
     * Example of firing an information event:
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
     *
     * Example of firing a warning event:
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
     */
    #endregion


    private string apiEndpoint_FinalUrl = "https://******************************";
    //ConvertedAccount.Name,ConvertedOpportunity.Name,ConvertedAccount.Id,ConvertedOpportunity.Id,

    private string LoginAuthEndpoint = "/services/oauth2/token";
    private string apiEndpoint = "**************************";

    private string userId = "***********************";
    private string password = "************";
    private string clientSecret = "C8D813B4848D89002EEE67302A510FB63F83";
    private string clientId = "3MVG9Eroh42Z9.iVP_TrtM4vmHry4wQzT_PPVJDWu1FJY";
    private string clientToken = "MU28eIN87d516REIQz";
    private string ProxyUrl = "******************.com:911";
    JObject jsonObject;
    /// <summary>
    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        /*apiEndpoint = Variables.apiEndpoint;
        userId = Variables.UserId;
        password = Variables.Password;
        clientSecret = Variables.ClientSecret;
        clientId = Variables.ClientId;
        clientToken = Variables.ClientToken;*/
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    public override void CreateNewOutputRows()
    {
        string AuthToken = GetToken(clientId, clientSecret, userId, password, clientToken, ProxyUrl, apiEndpoint, LoginAuthEndpoint);

        WebProxy proxy = new WebProxy
        {
            Address = new Uri(ProxyUrl),
        };
        ServicePointManager.Expect100Continue = false;
        ServicePointManager.SecurityProtocol |= SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
        HttpClientHandler clientHandler = new HttpClientHandler()
        {
            AllowAutoRedirect = true,
            AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip,
            Proxy = proxy,
        };
        HttpClient client = new HttpClient(clientHandler);
        HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, apiEndpoint_FinalUrl);
        request.Headers.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", AuthToken);
        HttpResponseMessage response1 = client.SendAsync(request).Result;
        try
        {

            if (response1.StatusCode == HttpStatusCode.OK)
            {
                string sfResponseString = response1.Content.ReadAsStringAsync().Result;
                // JObject obj = JObject.Parse(sfResponseString);
                jsonObject = JObject.Parse(sfResponseString);
                JArray jarr = (JArray)jsonObject["records"];
                //for (int i = 0; i < jarr.Count; i++)

                //{

                int cnt1 = 0;


                /*string ResultSetId = (string)jarr[i]["ResultSetId"];
                string TaskId = (string)jarr[i]["TaskId"];
                string TokenId = (string)jarr[i]["TokenId"];*/


                foreach (var item in jarr)
                {
                    Output0Buffer.AddRow();

                    cnt1++;
                    string Id = Convert.ToString(item["Id"]);
                    string Name = Convert.ToString(item["Name"]);
                  


                    Output0Buffer.ID = Id;
                    Output0Buffer.Name = Name;
                 



                }
            }





            //}
            else
            {
                // API call failed, raise an error
                string errorMessage = $"API request failed with status code: {response1.StatusCode}";
                ComponentMetaData.FireError(0, ComponentMetaData.Name, errorMessage, string.Empty, 0, out bool errorOccurred);
                throw new Exception(errorMessage);
            }
        }
        catch (Exception ex)
        {
            // Handle any other exceptions
            string errorMessage = $"An error occurred while making the API request: {ex.Message}";
            ComponentMetaData.FireError(0, ComponentMetaData.Name, errorMessage, string.Empty, 0, out bool errorOccurred);
            throw new Exception(errorMessage);
        }
    }




    public static string GetToken(string clientId, string clientSecret, string userId, string password, string clientToken, string ProxyUrl, string apiEndpoint, string LoginAuthEndpoint)
    {
        string token = "";
        //using (HttpClient client = new HttpClient())
        //{

        // ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        /*HttpClientHandler sfClientHandler = new HttpClientHandler();
        sfClientHandler.Proxy = WebRequest.GetSystemWebProxy();
        sfClientHandler.Proxy.Credentials = CredentialCache.DefaultCredentials; // or new NetworkCredential("username","password","DOMAIN");
        sfClientHandler.UseProxy = true;
        HttpClient sfClient = new HttpClient(sfClientHandler); */



        HttpContent sfRequestContent = new FormUrlEncodedContent(new Dictionary<string, string>
                         {
                         {"grant_type","password"},
                         {"client_id",clientId},
                         {"client_secret",clientSecret},
                         {"username",userId},
                         {"password",password + clientToken}
                         }
        );


        string response = string.Empty;
        WebProxy proxy = new WebProxy
        {
            Address = new Uri(ProxyUrl),
        };



        HttpClientHandler clientHandler = new HttpClientHandler()
        {
            AllowAutoRedirect = true,
            AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip,
            Proxy = proxy,
        };
        HttpClient client = new HttpClient(clientHandler);
        client.DefaultRequestHeaders.Accept.Clear();
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("*/*"));
        client.DefaultRequestHeaders.Add("Accept-Encoding", "gzip, deflate");
        HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, apiEndpoint + LoginAuthEndpoint);
        request.Content = sfRequestContent;
        ServicePointManager.Expect100Continue = false;
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12 | SecurityProtocolType.Tls;
        HttpResponseMessage response1 = client.SendAsync(request).Result;



        //if (response1.StatusCode == HttpStatusCode.OK)
        //{
        //    response = response1.Content.ReadAsStringAsync().Result;
        //}



        if (response1.StatusCode == HttpStatusCode.OK)
        {
            response = response1.Content.ReadAsStringAsync().Result;
        }
        JObject obj = JObject.Parse(response);
        token = (string)obj["access_token"];
        string ServiceUrl = (string)obj["instance_url"];



        //if (sfResponse.IsSuccessStatusCode)
        //{
        //  //  API call succeeded, extract the response content
        //    string data = sfResponse.Content.ReadAsStringAsync().Result;
        //    //var resp = JObject.Parse(sfResponse.Content.ReadAsStringAsync().Result);
        //    //token = resp.access_token;
        //    JObject obj = JObject.Parse(data);
        //WebProxy proxy = new WebProxy
        //{
        //    Address = new Uri(ProxyUrl),
        //};
        //ServicePointManager.Expect100Continue = false;
        //ServicePointManager.SecurityProtocol |= SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
        //HttpClientHandler clientHandler = new HttpClientHandler()
        //{
        //    AllowAutoRedirect = true,
        //    AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip,
        //    Proxy = proxy,
        //};
        //HttpClient sfClient = new HttpClient(clientHandler);
        //client.DefaultRequestHeaders.Accept.Clear();
        //client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("*/*"));
        //client.DefaultRequestHeaders.Add("Accept-Encoding", "gzip, deflate");
        //HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, apiEndpoint + LoginAuthEndpoint);
        //request.Content = sfRequestContent;
        //HttpResponseMessage sfResponse = client.SendAsync(request).Result;

        //HttpResponseMessage sfResponse = sfClient.PostAsync(apiEndpoint, sfRequestContent).Result;



        //   String sfResponseString = sfResponse.Content.ReadAsStringAsync().Result;

        try
        {
            //HttpResponseMessage response = client.GetAsync(apiEndpoint).Result;

            //if (sfResponse.IsSuccessStatusCode)
            //{
            //  //  API call succeeded, extract the response content
            //    string data = sfResponse.Content.ReadAsStringAsync().Result;
            //    //var resp = JObject.Parse(sfResponse.Content.ReadAsStringAsync().Result);
            //    //token = resp.access_token;
            //    JObject obj = JObject.Parse(data);
           //    token = (string)obj["access_token"];
            //}
            //else
            //{
            //    // API call failed, raise an error
            //    string errorMessage = $"API request failed with status code: {sfResponse.StatusCode}";

            //}
        }
        catch (Exception ex)
        {
            // Handle any other exceptions
            string errorMessage = $"An error occurred while making the API request: {ex.Message}";

       }
        //}
       return token;
    }
}

Answer 1

You need to use gacutil to register a copy of NewtonSoft dll into the global assembly cache (GAC).