0

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
EMW
  • 9
  • 5
  • Are you sure it's a space at the end of the line? The SSIS function `TRIM` [does not remove newlines and tabs](https://learn.microsoft.com/en-us/sql/integration-services/expressions/trim-ssis-expression). To see what it is, try adding a "!" to the field in a Derived Column Transformation. – steenbergh Mar 14 '18 at 19:42
  • Would I put the "!" at the end of the first part of the formula? TRIM([Provider Number]) == "" ? (DT_STR,10,1252)NULL(DT_STR,10,1252)! : [Provider Number] – EMW Mar 14 '18 at 20:34
  • No, you'd do this instead of the TRIM(). – steenbergh Mar 14 '18 at 21:00

1 Answers1

0

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.

KeithL
  • 5,348
  • 3
  • 19
  • 25