SSIS脚本组件以删除CHAR（n）字段中的“\0”charachers [英] SSIS Script Component to remove '\0' charachers in CHAR(n) fields

问题描述

我目前工作的一个数据库，有'\0在字段中的字符上。

I'm currently working on a database that have '\0' characters in fields.

有关实例字段

Category CHAR(4)

有时有值'\0\0\0\0'（4零字符）有时''（4空白字符）

sometimes has value '\0\0\0\0' (4 zero characters) and sometimes ' ' (4 blank characters)

我想用一个脚本组件的所有个体化这个问题的领域。
我已经写了下面的脚本，但它不工作，因为C＃转换\0\0\0\0空字符串。

I want to use a script component to individuate all the fields with this problem. I've written the following script, but it doesn't work since the C# converts the '\0\0\0\0' to an empty string.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Type rowType = Row.GetType();

    foreach (IDTSInputColumn100 column in ComponentMetaData.InputCollection[0].InputColumnCollection)
    {
        PropertyInfo columnValue = rowType.GetProperty(column.Name.Replace("_", ""));
        Object obj = columnValue.GetValue(Row, null);
        if (obj is string)
        {
            string s = (string)obj;
            StringBuilder sb = new StringBuilder();
            foreach (char c in s)
            {
                if (c < ' ')
                {
                    sb.Append(' ');
                }
                else
                    sb.Append(c);
            }
            columnValue.SetValue(Row, sb.ToString(), null);
        }
    }
}

是否有可能转换现场为一个字节数组而不是字符串，为了能够个体化\0'字符？

Is it possible to convert the field to a byte array instead of a string, in order to be able to individuate '\0' characters?

推荐答案

考虑炭（4）变换为二进制表示（使用数据转换组件），则操作从那里的各个元素。如果可能的话，将其丢在源查询，以便它已经二进制进入管道前。

Consider converting the char(4) to a binary representation (using Data Conversion component), then manipulating the individual elements from there. If possible, cast it in your source query, so that it's already binary before it enters the pipeline.

这篇关于SSIS脚本组件以删除CHAR（n）字段中的“\0”charachers的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！