DT_BOOL value of TRUE maps to 255 in a tinyint field. Why not 1???

Question

I am using SQL Server Integration Services 2008 to put a certain boolean value in the database, and originally I used the SSIS type of DT_BOOL (boolean).

However, in the database, the boolean value is being stored as a tinyint field (for legacy reasons). A boolean value of TRUE would be saved in the database as 255 (all bits on), instead of the more traditional value of 1.

Is there any way to force a DT_BOOL value of TRUE to map to 1 instead of 255? Or do I need to change the SQL field to bit, or use a numeric SSIS type like DT_UI1?

Answer 1

Well you could add a further transformation task into your SSIS package to transform the DT_BOOL value of TRUE to 1 and an SSIS quuivalent of the SQL Server data type tinyint but of course this adds an additional step to your processing.

You would be far better off modifying your database data type to the more appoprriate bit data type in my opinion if this is indeed an option to you.