| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2008-10-15 : 11:59:53
|
| Can anyone give me the lowdown on why the following situation happens and what can be done about it?When importing (witn SSIS) data to a table (sql server to sql server) the source is bit and the destination is decimal(2,0). The source has a value of 1 in the bit field but it comes over to the destination (in the decimal(2,0) field) as -1. I tried to search on here and google and couldn't find anything useful. Just curious why this happens and whether we should do anything about it. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-15 : 12:08:30
|
[code]DECLARE @i BITSET @i = 1SELECT @i, CAST(@i AS DECIMAL(2, 0))[/code]
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-10-15 : 12:17:41
|
| I see where this shows @i as 1 in both cases in your example. I was just curious why SSIS converts the 1 to a -1. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-15 : 13:07:29
|
quote:
Originally posted by Van
I see where this shows @i as 1 in both cases in your example. I was just curious why SSIS converts the 1 to a -1.
may be there are some other convertions or manipulations done before/after this to make it -1 |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-10-15 : 13:13:40
|
| I don't think there is. I created a test table in our DEV environment (SQL 2005) with the field as decimal(2,0) and imported the field from the source system (SQL 2000, bit field) with SSIS and it came directly over as -1. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-15 : 13:24:53
|
quote:
Originally posted by Van
I don't think there is. I created a test table in our DEV environment (SQL 2005) with the field as decimal(2,0) and imported the field from the source system (SQL 2000, bit field) with SSIS and it came directly over as -1.
are you doing this in data type conversion task or derived column task? what is expression used in it? |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-10-15 : 13:42:01
|
| There is a derived columns step but this field isn't one of them in the derived columns step. It just comes through as it does from the source. I went to the Advanced properties of the source (in SSIS) and it's coming in and going out as Boolean [DT_Bool]. Wonder if there's another type I sould tell it to go out as. Bit wasn't one lised. I assume Boolean [DT_Bool] is the representation for bit in SSIS. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-15 : 13:59:43
|
In the internal format of integer data types, the high order bit is used to determine whether a value is negative or positive.Many languages interpret any numeric value with the high order bit on as a negative. Since a bit column is only one bit, they see it as a negative one (-1).You might consider doing your conversion in TSQL, instad of in SSIS:select MyBit = convert(decimal(2,0),convert(bit,1))Results:MyBit ----- 1(1 row(s) affected) CODO ERGO SUM |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-10-15 : 14:03:35
|
| Ok, we're talking SQL here not Rocket Science...hehe. Thanks for the info though. Just gotta get my boss to buy in now. lol |
|
|
|
|
Bit field to decimal(2,0) field