How to: Query BIT Data Type in SQL Server and SSIS

If you have ever met problems when trying to export BIT data type columns to files or use them in SSIS, here are the tips for you.

The bit data type is an integer data type that can take a value of 1, 0, or NULL. Thus it is often used to represent Boolean type values such as Yes/No, True/False, and On/Off. However, BIT columns might be interpreted differently by different providers. When you query BIT columns in SSMS, it displays as 1 and 0 as its value. However, when you either query BIT columns in SSIS or export them to text files, it won’t be 1 or 0 any more. Below will show you some tricks to handle BIT columns.

How SSIS handle BIT columns?

In SSIS, BIT columns are interpreted as Boolean [DT_BOOL]. How to tell this?

You need right click the OLE DB Source component and choose “Show Advanced Editor”. Then click the “Input and Output Properties” Tab. Expand “Output Columns” under OLE DB Source Output. You will notice that the BIT column is Boolean type as shown below. Here column IsSHP is the BIT data type column.

So when you need to query BIT column, you need to directly use TRUE or FALSE. Below shows that how you can query BIT columns in the conditional split task.

Note that there is no quote around true in the expression.

How to export BIT columns to files as integers in SSIS?

Solution 1: Using Derived Columns

Or if you want to store it with less storage, you can use DT_UI1 data type.

IsSHP_bit = [IsSHP] ? (DT_UI1)1 : (DT_UI1)0

Solution 2: Tweak OLE DB Source

First of all, you need to convert BIT columns in OLE DB connection manager from OLE DB Source Editor,

select convert(int, IsSHP) as IsSHP

Then change the DataType in the OLE DB Source to integer type DT_I4 as shown below.

Now you will get 1 or 0 as output for BIT columns.

That’s it and hope it will help!