SSIS Data Conversion task is giving wrong output when source datatype is BIT

Question

I have created two tables from below script:
CREATE TABLE Test (ISDELETED BIT)
CREATE TABLE Test1 (ISDELETED INT)

Table Test data:

Here datatype of column isdeleted in table test is BIT and in table test1 datatype is INT. So now using SSIS I am trying to load data from test table into Test1 table.

1. screenshot of DFD: Source is TEST and Destination is Test1
2. Bit to int conversion
3. Mapping of converted column with destination

After running this package getting below output in Test1 table:

So as output I am getting data -1 instead of 1.
I think this is SSIS bug. If anyone has a solution on the above problem then please help.

1: /api/attachments/47966-image.png?platform=QnA 3: /api/attachments/47967-image.png?platform=QnA

Answer 1

Hi @Anagha Adhav ,

1.The bit data will be changed as Boolean [DT_BOOL] data in SSIS package.

Please refer to Mapping of Integration Services Data Types to Database Data Types.

2.We can use Derived Column Transformation in Data Flow Task.

Useful expression in Derived Column:

(DT_WSTR,10)ISDELETED == "True" ? 1 : ((DT_WSTR,10)ISDELETED == "False" ? 0 : NULL(DT_I4))

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 2

I am not sure if you need SSIS Data Conversion transformation for your case.
BIT data type is a subset of INT data type. So it should work as-is.

You should be able to simplify the DFT Task by removing the SSIS Data Conversion transformation.