使用SSIS和ADO.NET更新SQL Server的nvarchar(max)列遇DT_TEXT转换问题

方案一：修复Script Task中的DT_TEXT转换逻辑 #

你遇到的类型映射错误是因为直接传递BlobColumn对象给ADO.NET参数导致的，乱码则是编码不匹配的问题。按以下步骤处理即可：

1. 读取DT_TEXT列的二进制数据：

   // 从BlobColumn中提取字节数组
   byte[] blobContent = new byte[(int)Row.DDL.Length];
   using (var stream = Row.DDL.GetBlobData(0, (int)Row.DDL.Length))
   {
       stream.Read(blobContent, 0, (int)Row.DDL.Length);
   }
   

2. 用匹配源数据的编码转换为字符串：
   如果你确定源文本是UTF-8编码，用Encoding.UTF8；如果是GBK等中文编码，用Encoding.GetEncoding("GBK")。示例：

   string ddlContent = Encoding.UTF8.GetString(blobContent);
   

3. 配置ADO.NET参数以支持nvarchar(max)：
   必须指定参数类型为SqlDbType.NVarChar并设置Size = -1，否则会被截断或报错：

   using (SqlConnection conn = new SqlConnection("你的ADO.NET连接字符串"))
   {
       conn.Open();
       string updateSql = "UPDATE YourTable SET TargetColumn = @DDL WHERE ID = @RecordID";
       using (SqlCommand cmd = new SqlCommand(updateSql, conn))
       {
           cmd.Parameters.Add("@DDL", SqlDbType.NVarChar, -1).Value = ddlContent;
           cmd.Parameters.Add("@RecordID", SqlDbType.Int).Value = Row.ID;
           cmd.ExecuteNonQuery();
       }
   }
   

方案二：无需脚本的可视化组件方案 #

如果不想写脚本，用SSIS内置组件就能搞定：

• 第一步：转换数据类型
  添加「数据转换」组件，把源DT_TEXT列转换为DT_NTEXT类型——这个类型对应SQL Server的nvarchar(max)，天生支持Unicode和超长文本，不会有长度限制。
• 第二步：用ADO.NET Command执行更新
  拖拽「ADO.NET Command」组件到数据流，配置Azure SQL Server的ADO.NET连接管理器，编写更新语句：

  UPDATE YourTargetTable 
  SET TargetColumn = ? 
  WHERE PrimaryKey = ?
  

  然后在参数映射里，把转换后的DT_NTEXT列映射到第一个参数，主键列映射到第二个参数即可。参数顺序要和SQL语句里的占位符顺序一致。

重要提示 #

• 编码匹配是解决乱码的核心：如果不确定源编码，可以先尝试Encoding.Unicode.GetString(blobContent)（对应UTF-16，SQL Server的nvarchar默认用这个编码）。
• 使用ADO.NET Command时，参数不要手动设置固定长度，设为-1或者留空，SQL Server会自动识别为nvarchar(max)。

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