Data Warehousing and Business Intelligence

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

7 Comments »

  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:
      AppCode|ActiveFlag
      GDS|True
      BBT|True
      CFI|False

      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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: