SSIS同步DB2至Oracle时识别乱码varchar字段置空问题咨询

问题A：Oracle侧检测char/varchar不兼容字符的实现方法 #

Oracle中char/varchar2类型支持的字符范围由数据库NLS_CHARACTERSET参数决定，所有无法被该字符集无损编码的字符，都会导致写入varchar类型字段时报错，可通过以下方式检测：

• 12c及以上版本直接使用内置VALIDATE_CONVERSION函数，判断字符串是否可以无损转换为目标varchar2类型，返回1代表合法，0代表存在不兼容字符：

-- 检测字段内容是否可无损存入当前库的varchar2类型字段
SELECT 
  source_col,
  VALIDATE_CONVERSION(source_col AS VARCHAR2(4000)) AS is_varchar_compatible
FROM source_table;

可以直接在写入逻辑里把不兼容的字段值置为NULL：

INSERT INTO target_varchar_table (col1, col2)
SELECT
  other_col,
  CASE WHEN VALIDATE_CONVERSION(nvarchar_col AS VARCHAR2(4000)) = 1 
       THEN CAST(nvarchar_col AS VARCHAR2(4000))
       ELSE NULL END
FROM nvarchar_stage_table;

• 11g及以下低版本Oracle，可通过CONVERT函数对比实现检测：将字符串尝试转换为当前库NLS_CHARACTERSET编码，转换过程中非法字符会被替换为?，对比转换前后的字符串即可判断是否存在非法字符；也可以自定义转换函数，捕获ORA-01482（unsupported character set）、ORA-12899（value too large for column，多由多字节字符编码长度溢出触发）异常返回校验结果。

问题B：SSIS侧字段级错误捕获、不丢弃整条记录的实现方法 #

不需要依赖包级别的try/catch机制，SSIS数据流本身支持字段级的错误处理，完全可以实现单字段异常时仅将该字段置NULL，不影响整条记录的其他字段写入，推荐两种实现方式：

• 方式一：脚本组件逐字段校验（最灵活，推荐）
  1. 在DB2源组件和Oracle目标组件之间，拖入类型为「转换」的脚本组件
  2. 在输入列配置页，将所有需要做字符校验的字段设置为读/写权限
  3. 编辑脚本，在Input0_ProcessInputRow方法中对每个目标字段单独加异常捕获，尝试将源字符串转换为Oracle varchar对应编码（比如ZHS16GBK对应代码页936，AL32UTF8对应代码页65001），转换失败或出现字符丢失时直接将该字段置为NULL，示例代码：

  public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
      Encoding targetEncoding = Encoding.GetEncoding(936); // 替换为目标Oracle varchar对应的实际代码页
      // 逐个处理需要校验的字段，示例为Desc字段
      if (!Row.Desc_IsNull)
      {
          try
          {
              byte[] encodedBytes = targetEncoding.GetBytes(Row.Desc);
              string decodedStr = targetEncoding.GetString(encodedBytes);
              // 转码后和原字符串不一致，说明存在无法兼容的字符
              if (decodedStr != Row.Desc)
              {
                  Row.Desc_IsNull = true;
              }
          }
          catch
          {
              // 转码抛异常直接置NULL
              Row.Desc_IsNull = true;
          }
      }
  }
  

• 方式二：利用数据转换组件的错误输出
  在源和目标之间加入数据转换组件，将源字段转换为和Oracle varchar匹配的DT_STR类型（指定对应代码页），打开组件的「错误输出配置」页，将对应字段的错误处理设置为「重定向行」，将错误流重定向到派生列组件，在派生列中把出错的字段赋值为NULL，最后用Union All组件把正常流和处理后的错误流合并，再写入Oracle目标即可。

注意：SSIS连接Oracle时建议使用Oracle官方发布的ODAC驱动，不要用微软自带的旧版OLE DB Provider for Oracle，能避免大量隐式字符集转换导致的非预期报错。

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