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

AnaghaV 1 Reputation point
2020-12-14T13:32:11.243+00:00

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

Table Test data:

47957-image.png

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 48021-image.png
  2. Bit to int conversion 47993-image.png
  3. Mapping of converted column with destination 47920-image.png

After running this package getting below output in Test1 table:

48012-image.png

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,958 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 20,721 Reputation points
    2020-12-14T14:22:37.973+00:00

    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.

  2. Monalv-MSFT 5,691 Reputation points
    2020-12-15T04:11:15.177+00:00

    Hi @AnaghaV ,

    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.

    48207-datatype.png

    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))

    48261-df.png

    48262-oledbsou.png

    48214-checkdatatypeinadvancededitor.png

    48116-derivedcolumn.png

    48117-result.png

    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?