Search code examples
t-sqlssis-2012

Converting a bit to an int returns a negative value

I am trying to convert a bit field from the source DB into an integer value in the Data Warehouse in a SSIS Project (SQL Server 2012)

Select cast([MyField] as int) as TheField from MyTable

For some reason this returns 0 and -1, as opposed to the +1 I am expecting. Even 

Select abs(cast([MyField] as int)) as TheField from MyTable

will yield the -1 in the destination table. The SELECT statement works as intended in the preview as well as in SSMS and I see the desired 0 and 1. However the actual Data Transfer task in the SSIS Package isn't working as intended. I tried the data conversion task and a direct writing into the table without the task.

I really don't want to use an additional update statement to multiply my int values with -1.

As this may be a little confusing, here is the source table:

CREATE TABLE [dbo].[empl](
[Name] [nvarchar](100) NULL,
[active] [bit] NULL
) ON [PRIMARY]

GO

Here is the screenshot of the table:

enter image description here

And the screenshot of the data transfer task:

enter image description here

The destination table with the result of the Data transfer and conversion:

enter image description here

Solution

  • very interesting, if you put a data viewer you will see that downstream, MyField is either TRUE or FALSE, so what you can do is add a "derived column" component to create a new column with the expression:

    MyField == TRUE ? 1 : 0

    then map this new column to the destination