How to Load Bit datatype Columns from SQL Server Table to Oracle Table using SSIS

Scenario: I am loading a table that contains a bit datatype column from SQL Server into an Oracle Table with number datatype. However, when I load the data using SSIS, the 1's gets converted to a -1 in Oracle Number datatype.


Description: In SQL Server the bit datatype column contains values 0's or 1's but we don't have a corresponding bit Datatype in Oracle. Some Oracle table design could use NUMBER (1) or use a VARCHAR2(5) [for 'True'/'False']. For performance reasons better to use NUMBER(1) though.


Solution

In SSIS, the bit data type from SQL Server gets converted to 'TRUE' or 'FALSE' in SSIS data type before any other conversion. Since there is no boolean datatype in Oracle, you have to use SSIS Data Conversion transformation to first convert the Bit to Numeric Datatype. While in Numeric datatype conversion, for some reason SSIS converts the 1's to -1's and 0's to 0's. To resolve this mishap. Use the Derived Column Transformation. To ensure you are converting the bit correctly use Derived Column Transformation to convert the BIT into 0's, 1's, or NULL  depending on the data source value.

In this way your Oracle table will be sure to have those 0's or 1's from source.


Details

too come


Summary