SSIS Converting a char to a boolean/bit

Question

I have an SSIS package to load data; as you may recall there are flags that are in data files as Y/N char(1) when I am trying to load them as bit flags into SQL Server. I am specifying the columns in the data file as String [DT_STR] and I have a data conversion task to convert them to booleans based on the following expression (I received the same conversion error just specifying them as DT_BOOL to begin with, despite SSIS asking me to say what values it should consider as boolean):

[ColumnName] == "Y" ? (DT_BOOL)1 : (DT_BOOL)0

Running the package gives an error and tells me Invalid character value for cast specification and The value could not be converted because of a potential loss of data on the actual import to SQL Server (via an OLE DB Destination).

What am I missing here to get it to properly convert?

Answer 1

I had the same problem with

(DT_BOOL)([ColumnName] == "Y" ? 1 : 0)

and I could only get it to work by taking OUT the "(DT_BOOL)" portion out of the expression and putting the job of converting it to a boolean onto the "Data Type" part by selecting "Boolean [DT_BOOL]. No problems after that.

Answer 2

I was able to solve it by using a derived column and, instead of replacing the char columns, creating new columns set to type of DT_BOOL like so:

[Recycled] == "Y" ? True : False

Answer 3

Try this:

(DT_BOOL)([ColumnName] == "Y" ? 1 : 0)

This also has the advantage of automatically setting the data type of the derived column correctly.