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 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