Data Warehousing and Data Science

16 November 2011

SSIS: True/False and 1/0 values on Bit Columns

Filed under: SSIS — Vincent Rainardi @ 11:13 pm

The column is defined as bit on a SQL Server table. You created an SSIS package to export the table to a text file. In Mgt Studio when you query the table, the bit column displays 1 and 0 as its value, but in the output text file they are true and false.

You looked everywhere for what might have caused it to be true/false. You suspected the text file connection. But it is defined as string[50] there. So you changed the SQL query inside the workflow from

select column1 from table1

to: select convert(int, column1) from table1

But it is still the same. The output is still true/false not 1/0 as you see in Mgt Studio.

So you convert to varchar like this:

select convert(varchar, convert(int, column1)) from table1

You hoped that it will now be 1 and 0, but alas no. It is still true and false.

So you changed the data type in the text file connection from string to four byte signed integer.

What you get is -1 for true and 0 for false. Yes! Not 1 and 0 but -1 and 0.

So you changed the data type in the text file connection to four byte unsigned integer. Ha this will force it to be 1 and 0 you thought.

But alas, no! What you get is a very big number for true and 0 for false.f

So here the secret: right click on the OLE DB Source component in the data flow, and choose Show Advanced Editor. Go to the last tab, Input and Output Properties:

Go to OLE DB Source Output, Output Column (NOT External Columns!), then select the bit column. Change the DataType from Boolean to Four Byte Signed Integer.

Change your query to:

select convert(int, column1) as column1 from table1

Note: If you don’t the above the output will be -1 and 0, not 1 and 0.

And execute the data flow.

Voila, you get 1 and 0 in the output text file.

Note: whether in the flat file connection you defined the output columns as boolean or string or signed int, it will still be 1 and 0.

Hope it helps. As usual I welcome comments and corrections.

Vincent Rainardi, 16/11/2011


  1. This 1 and 0 thing had given me a lot of trouble when I was new to SSIS 🙂

    Comment by theSuda — 17 November 2011 @ 5:37 am | Reply

  2. This totally worked, awesome post

    Comment by Priyanka — 29 March 2012 @ 7:02 pm | Reply

  3. These booleans were giving me trouble till i read your post. Your solution works like a charm.

    Comment by the maniac — 4 December 2013 @ 5:39 pm | Reply

  4. This solution works perfectly while creating packages using BIDS. But how about data transfer between tables and/or Servers using Import Export Wizard in Management Studio? I am facing similar issue using the wizard, but unable to find an appropriate solution. Any help would be appreciated.

    Comment by RD — 18 March 2014 @ 3:02 pm | Reply

    • Hi RD,
      the Export Wizard works as it is, as follows:

      create table AppStatus (AppCode varchar(20), ActiveFlag bit)
      insert into AppStatus (AppCode, ActiveFlag) values (‘GDS’, 1)
      insert into AppStatus (AppCode, ActiveFlag) values (‘BBT’, 1)
      insert into AppStatus (AppCode, ActiveFlag) values (‘CFI’, 0)

      1. Right Click database, Tasks, Export Data, Next
      2. Choose a Data Source: SQL Server Native Client 11.0, Next
      3. Destination: Flat File Destination, File name: c:\Output.txt, Code Page: 1252, Format: Delimited, Text Qualifier: None, Column names in the first data row, Next
      4. Copy data from one of more tables, Next
      5. Source table: AppStatus, Row delimiter: {CR}{LF}, Column delimiter: Vertical Bar {|}
      6. Edit Mapping, AppCode: string [DT_STR] 20, ActiveFlag: Boolean [DT_BOOL] 10, OK, Next
      7. Run immediately, Next, Finish

      c:\Output.txt is like this:

      I’m using SQL Server 2012 Developer Ed.

      If your issue happens on 2008/R2, try this: on step 7 Save as SSIS package. Then modify the package as per my article before running it.

      Comment by Vincent Rainardi — 23 March 2014 @ 8:03 am | Reply

  5. Fantastic…this was driving me up the wall. Thanks. 🙂

    Comment by Phil T. — 5 March 2015 @ 3:34 pm | Reply

  6. […] SSIS: True/False and 1/0 values on Bit Columns […]

    Pingback by 在Package中处理 bit column-IT大道 — 19 January 2016 @ 4:03 am | Reply

  7. However, the best way to deal with this is to… let the connection managers and components work as they have been designed to work. That means no conversions throughout. If you use the right data types, you won’t have problems. That is, you export a BIT field to a file as DT_BOOL and you import into a SQL table as DT_BOOL – as simple as that. If the destination table has a BIT field, it’ll understand that ‘True’ means 1 and ‘False’ means 0. No stunts necessary. Try it.

    Comment by Darek — 26 January 2017 @ 11:19 am | Reply

  8. Awesome solution. Was banging my head for the last hour

    Comment by NA — 25 January 2018 @ 7:57 pm | Reply

  9. Using CAST(MyBitColumn AS VARCHAR(1)) and leaving the output definition as string will work equally well. Or, if you’re using a staging table before output, make the bit field a VARCHAR(1).

    Comment by Mark Roworth — 18 August 2020 @ 3:52 pm | Reply

  10. Hey! thanks for the solution. But you did not mention why this is happening why 1 is converted to -1?

    Comment by Parag Bagga — 13 July 2021 @ 7:20 pm | Reply

    • Why 1 in bit data type is converted to -1 in signed int data type.
      Hi Parag, in signed integer, the first bit is used to represent the minus. So the first bit is negative. For example in three bits the first bit means -4 (not +4), the second bit means +2 and the last bit means +1. So 100 means -4+0+0=-4, 101 means -4+0+1=-3, 110 means -4+2+0=-2, 111 means -4+2+1=-1, 011 means 0+2+1=3. So the number range from -3 to +3. In unsigned integer, the first bit means positive. In three bits the number ranger from 0 to 7, i.e. 111 means +4+2+1=7, 011 means 0+2+1=3.
      In SQL Server (and indeed any other system), bit is unsigned integer. If it is converted to signed integer, the first bit becomes negative, because it is used to represent the sign. That is why 1 in bit data type is -1 in signed int data type.

      Comment by Vincent Rainardi — 14 July 2021 @ 4:41 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: