Menu
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?
941
DT_I4: It is a four-byte, signed integer. DT_NUMERIC: An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign.
Implicit Conversion Implicit conversions are not visible to the user. Data types are automatically converted from one data type to another. For example, when a string is compared to an int, the string is implicitly converted to int before the comparison proceeds: 1.
The CHAR and VARCHAR data types used in SSIS are DT_WSTR, with a maximum length of 4000 characters. In SSIS, Vertica strings are converted to Unicode strings in SSIS to handle multi-lingual data. You can convert these strings to ASCII using a Data Conversion Task.
Try this:
(DT_BOOL)([ColumnName] == "Y" ? 1 : 0)
This also has the advantage of automatically setting the data type of the derived column correctly.
183
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With