MS SQL Server bit column exported as boolean

Question

I'm trying to export data from SQL Server to flat files.

The data includes a bit column 0/1 and I need it as is but it get exported as Boolean TRUE/FALSE which is causing ETL bulk insert to fail.

I tried changing the data mapping to single byte integer, float, numeric, string, text hoping to get a simple 0/1 but nothing worked.

Any pointers on how to solve this are appreciated (other than replacing text within the resulted file).

Ensconce

Is this relevant dwbi1.wordpress.com/2011/11/16/… – Trela

Perhaps a case statement on export to cast as INT? – Ecphonesis

@scsimon I'm using the export wizard there's no option to add a case statement. Or is there another way to export? – Ensconce

Answer 1

Add a IIF in your select to convert Boolean to 0/1 to export:

SELECT Name, Code
    ,IIF(EuroZone = 1, 1, 0) AS EuroZone
    ,IIF(Visible= 1, 1, 0) AS Visible
FROM your_table

Gillian

Answer 2

The problem is, SQL Server has a bit data type. It does not have a boolean, while SSIS uses .Net data types, not SQL data types. It supports boolean, not bit. Though it has built-in conversions, to resolve problems like that. So in my opinion, you need to use a derived column to solve that.

Infirmary