MS SQL Server bit column exported as boolean

user_559465 0
Viewed 1560

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.

enter image description here

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

export-to-csv sqldatatypes export sql-server database
2 Answers

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
user_27978930

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.

user_106380150
Related