2

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

For instance the field

Category CHAR(4)

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

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);
        }
    }
}

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

sergiom
  • 4,791
  • 3
  • 24
  • 32

2 Answers2

0

Do you need to do this in a script component? Could you not use a Derived Column task and use Substring to pull out each of the column values? You could use Trim as well to remove any spaces.

grapefruitmoon
  • 2,988
  • 1
  • 22
  • 26
  • using a script component allows me to write it in a generic way, just iterating an all columns instead of writing the code for every single column. I've solved the problem accessing the data using a View that replaces '' values. But I still wander if it is possible to do it using a Script Component. – sergiom Dec 03 '10 at 14:27
0

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.

Mark
  • 9,320
  • 6
  • 57
  • 70