I have a Boolean field in a source table which JDM interprets as data type tinyint and is then manually overridden to data type char(1) from source to staging. I am trying to then do a transformation on the field to convert the 0's to N and 1's to Y, but neither of the methods I've tried so far are working. Any ideas on how to do this correctly?
First try was using a Replace Characters transformation with Type = CharType, Replace Value = 1, Replace Text = Y. This one doesn't generate any errors upon execution, but also doesn't replace the data in the field.
Second try was using a Custom Value transformation with the following statement: IF [FieldName] = '1' THEN [FieldName] = 'Y' ELSE [FieldName] = 'N'. This one generates a syntax error upon execution, but I cannot find any info on the Support Site on what the correct syntax should be.