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
Subscribe to:
Post Comments (Atom)
-
Hi I was recently working on extracting a table that has 400 columns from a SQL Server DB to load into an Oracle Target Database. I design...
-
In this blog post I will show you how to implement your own custom logging in Azure Data Factory Before you do this please note that Azur... -
Scenario: I want to load data from a SQL Server table that has 1.7billion rows containing a column with data type varchar(max) which is popu...
No comments:
Post a Comment