4

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

Andre Doose
  • 161
  • 10

1 Answers1

4

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

Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • Would this be faster than a subsequent SQL Statement Update table set field = field * (-1) ? The only values I have are 0 and -1. – Andre Doose Jun 22 '16 at 09:55
  • I've deleted my answer. This is the best solution. – BIDeveloper Jun 22 '16 at 10:01
  • the performance difference would be marginal, the only advantage of this solution is to be a bit (no pun intended :) neater since acts as a data conversion from TRUE/FALSE which are the actual values that will always be in the pipeline regardless the original value in myField – Jayvee Jun 22 '16 at 10:02
  • Very well, I will go with the derived column. It's still interesting how a "yes" value is converted into -1 instead of 1, but that's apparently a feature, not a bug :-) – Andre Doose Jun 22 '16 at 10:04