SSIS 2016 C#组件加载Newtonsoft.Json 6.0.0.0失败求助

错误信息：

System.IO.FileNotFoundException: 无法加载文件或程序集“Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed”或其依赖项，系统找不到指定文件。

环境信息：

• SSDT版本：Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64)
• 开发限制：必须使用VS2016（服务器版本限制），在VS2017中使用Newtonsoft.Json v9时包可正常运行

脚本组件代码：

#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://******************************";
    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;

    public override void PreExecute()
    {
        base.PreExecute();
        /*apiEndpoint = Variables.apiEndpoint;
        userId = Variables.UserId;
        password = Variables.Password;
        clientSecret = Variables.ClientSecret;
        clientId = Variables.ClientId;
        clientToken = Variables.ClientToken;*/
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    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;
                jsonObject = JObject.Parse(sfResponseString);
                JArray jarr = (JArray)jsonObject["records"];

                int cnt1 = 0;
                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
            {
                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)
        {
            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 = "";

        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;
        }
        JObject obj = JObject.Parse(response);
        token = (string)obj["access_token"];

        try
        {
            // 注释代码已保留原结构
        }
        catch (Exception ex)
        {
            string errorMessage = $"An error occurred while making the API request: {ex.Message}";
        }

        return token;
    }
}

解决方案 #

方法1：程序集版本重定向（推荐） #

让SSIS运行时自动将对v6.0.0.0的请求重定向到实际使用的v9版本：

1. 在SSIS项目中添加app.config文件（若不存在）
2. 编辑app.config，添加以下配置：

<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" 
                          publicKeyToken="30ad4fe6b2a6aeed" 
                          culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-9.0.0.0" 
                        newVersion="9.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

3. 在VS2016中，右键app.config → 属性，将复制到输出目录设为始终复制
4. 将Newtonsoft.Json.dll（v9版本）复制到SSIS运行时目录：
   • 32位：C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn
   • 64位：C:\Program Files\Microsoft SQL Server\130\DTS\Binn

方法2：匹配SSIS期望的版本 #

直接安装Newtonsoft.Json v6.0.0.0版本，消除版本差异：

1. 在脚本组件的NuGet包管理器中执行：

Install-Package Newtonsoft.Json -Version 6.0.0

2. 右键脚本组件中的Newtonsoft.Json引用 → 属性，确保复制本地设为True

方法3：嵌入程序集到脚本组件 #

将Newtonsoft.Json嵌入到脚本的程序集中，避免外部依赖查找问题：

1. 右键脚本组件中的Newtonsoft.Json引用 → 属性
2. 将嵌入互操作类型设为True，复制本地设为True
3. 重新编译脚本组件

内容的提问来源于stack exchange，提问作者sree