SSIS bit Fields? True/False vs 0/1
-
OK I'm trying to do stuff outside of my comfort zone of TSQL.
I made my first SSIS project, which simply exports my TallyCalendar table to a file.
On the one hand it works! so i'm happy, but on the other hand, i wanted to export zeros and ones as 0/1, and not true/ false for the bit fields that are in the database.
i thought that by making my "flat file connection manager" define the columns as int*, i'd be ok; but it's clear that some where a BIT field is converted to true false.
i could ignore it and import back i'm sure, but i'm trying to push and understand boundaries, more than know i can import it back.
so...do i ignore it and move on?
Is this a "Derived column" situation i should read up on?
My ADO.NET connection points at a table...i would guess that if i change it to a SQL or something,a nd cast the column to int i'd get zeros and ones?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell
The trouble with bit values is that not every provider interprets, as SQL Server does, 1 as True and 0 as False. For that reason, I always stick with "True" and "False" when using Integration Services. If you want to export 1s and 0s to your file, then I think the safest way is to use a derived column, as you hinted.
John
-
If you use a SELECT as the source of your SQL data, you could CAST the BIT to INT as part of the select and then everything should happen just as you want.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
One other angle to consider that helped me...a SQL Server BIT is an exact numeric data type, however once in SSIS it maps into a .NET Boolean data type which is not a numeric data type.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Learning a lot today guys, thanks for the input.
I did as Phil suggested,and changed my datasource to use an OLEDB Source, made the SQL do the conversions, and it exports as zeros and ones;
When trying to use the Derived Column, I can't seem to get that to work; it's just not intuitive to me how to get teh destination data source to accept something that was converted.
for examaple, i tried converting my bit fields via any of the math functions FLOOR,ROUND etc and got the issue exactly as opc.three described...they don't count as numeric fields, so those functions don't work and i get an error(as expected, i guess).
when using (am i using it right?) some of the conversions, whether to DT_i2 or dt_numeric, i get the same implicit conversion and true/false in my file results.
I was trying to change to a string or maybe an expression, but for me i'm thinking i need to change the datatype somehow so it no longer says Boolean[DT_BOL] as the destination? data type...that seems to be where my understanding is lacking. i thought if i used an IIF equivilent expression, like ((IsHoliday==true) ? "1","0"), but i'm still fighting with syntax and mentally locked on the destination data type.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Another tricky (not intuitive) thing with Booleans in SSIS when coming from T-SQL is that you do not need to do any equality test. Consider something that looks like this:
@IsHoliday ? (DT_I4)1 : (DT_I4)0
or
!@IsHoliday ? (DT_I4)0 : (DT_I4)1
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Lowell
I think Phil's suggestion is better than using a derived column. But I think the syntax for the latter would be something like this: ((IsHoliday=="True") ? 1, 0).
John
-
Ultimately, though I also find it aggravating, what it comes down to is this:
A bit value is either 0 or 1.
A boolean value is either True or False.
SQL Server has a bit data type. It does not have a boolean data type. Non-nullable bits are frequently used in SQL Server to simulate boolean data.
SSIS uses .Net data types, not SQL data types. It supports boolean, not bit. It has certain built-in auto-conversions, among which is an auto-conversion of bits to Nullable<bool>.
Since flat files really only store string data, all values are converted to strings when exporting to flat files. In .Net, converting a boolean to a string results in "True" or "False" (or possibly null if using Nullable<bool>, which gets converted to empty string on final export).
I think the best option of you want your bit fields to look like bit fields in your flat files is to use the previously-suggested ternary operator to create a new derived column. (UI1 has a smaller memory footprint than I4):
IsFlagged_bit = [IsFlagged] ? (DT_UI1)1 : (DT_UI1)0
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply