Beware the bit

This is probably unique to SQL Server, so if you don't use SQL server, you can probably ignore this tip.

Beware the bit data type. It's a curious thing. This just bit me in the butt, so I thought I'd share it.

If you pull in a bit datatype from a source, Informatica converts it to a string (1) in the qualifier. Odd, considering a bit is either 0 or 1.

If you try to write that to an int target, you'll always get 0 - why? Because the string(1) doesn't store "0" and "1", it stores "T" and "F".

When I get some free time (ha!) one of these days, I'm going to change the qualifier to a string(10) just to see if it actually stores "TRUE" and "FALSE", but I suspect it doesn't, since it defaults to just a string(1).

But, if you're going to write a bit to your target, you'll need to use an expression, something like:

IIF(bit_Flag = 'T', 1, 0)

Consider yourself warned. :)