Search code examples
sqlssisetlremoving-whitespacederived-column

Derived Column in SSIS - Trailing Space

I have tried several ways to format a derived column to eliminate trailing spaces. To format the column when just viewing the column in Excel and transferring to Access was: 

'=TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160),"")))

In the derived column I've tried:

- REPLACE(RTRIM([Provider Number]),"\\x00A0","") == "" ? (DT_STR,10,1252)NULL(DT_STR,10,1252) : [Provider Number]

- TRIM([Provider Number]) == "" ? (DT_STR,10,1252)NULL(DT_STR,10,1252) : [Provider Number]

- LTRIM(RTRIM([Provider Number]))

When I query the length for the column my results still count 1 extra space when the package runs (successfully):

Results

Solution

  • It looks like you know what you are trying to trim but you are having difficulty use ascii value in SSIS.

    I can't seem to figure out how to use ASCII in SSIS however you can run it through a script component. Make sure you check on inputs that you want to Read/Write ProviderNumber.

    simple code:

    Row.ProviderNumber = Row.ProviderNumber.TrimEnd((char)160);

    //Add .Trim() to clear all white space

    Added due to comment:

    After you run it through C# then you can do your derived column

    TRIM([Provider Number]) == "" ? NULL(DT_STR,10,1252) : [Provider Number]

    TRIM might be excessive here, but doesn't really hurt.