question

jangi avatar image
jangi asked

Datatype issue

Hello, I am exporting a table from sql server to Excel by using SSIS(using oledb source and excel destination) One of the column datatype is Bit (it should show result 1 when it is true and 0 when it is False) but in excel result I got -1 instead of 1. I was totally confused and I even tried to convert that into INT which places an error in Excel destination task when I run the package error: Column "Active" cannot convert between Unicode and non unicode string datatypes Can someone help please
sqlssisexcel
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
Every programming language except VB uses 1 byte to store bit information, setting the rightmost bit to 1 and other 7 bits to 0 for true, and all 8 bits to 0 for false. This results in evaluation of true to 1 and false to 0. In VB (or VBA), the way the boolean values (close equivalent of bit in SQL Server) are handled is different. In these languages, 2 bytes are used, setting all 16 bits (2 times 8 = 16) to 1 for true, and all 16 bits to 0 for false. If you take 2 bytes and set every bit to 1 then the resulting number (in hexadecimal notation) is 0xFFFF. This number evaluates to (surprise) -1. You can try this in T-SQL to check it: select cast(0xffff as smallint) TrueInVBA, cast(0x0000 as smallint) FalseInVBA; -- the above returns this: TrueInVBA FalseInVBA --------- ---------- -1 0 The reason I chose smallint was because it takes 2 bytes, so the script above mimics the VBA behaviour. One way to address your issue is to replace the bit type column with something like this: cast(ThatBitColumn as int) as ThatBitColumn In other words, do the conversion part before, not after. This way, the column will appear as a whole number (integer) to Excel and you can map it. Hope this helps. Oleg
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I used convert(int, column) And it’s working thank you
0 Likes 0 ·
Jon Crawford avatar image
Jon Crawford answered
Thank you for that excellent explanation Oleg, I understand my own database a little better now.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.