timestamp(6) oracle datatype issue while loading the data from one Oracle table to another using SSIS

I am trying to load the data from one oracle table to another using Data flow task in SSIS. Both source and destination oracle tables has the same datatype (timestamp(6)). Once the DFT is completed, I am seeing the invalid date format in target table.

source table date format:
19-SEP-23 04.20.18.000000000 PM

destination table data format:
19-MAR-42 09.48.32.925907504 PM

I have tried using all the date related type cast function but still the target data is not matching with source.

Application : Visual studio

Source and destination : MS oracle source, MS Oracle destination

Mappings: Direct source to target mappings

Data access mode: table name – fast load (using direct path)

When I checked the metadata, date type was coming as DT_STR with length 75 even tough the datatype in the source column is datetime(6). I have changed the output column data type to dt_dbtimestamp2 and scale 6 in the advance editor options.

Then when I used the Derived column transformation Below is the output I am receiving

(DT_DBTIME)(column1) –> 16-FEB-24 04.20.18.000000000 PM (time format is coming correct but the date is not matching)

(DT_DBDATE)(column1) –> 19-SEP-23 12.00.00.000000000 AM (Date is matching but the time format is not matching)

(DT_DBTIMESTAMP2,6)(column1) –> data is not loading in the target table. In the execution result, it is showing that the rows are writing to target table but not data is not loading in the target table.

I have also used the data conversion task but I am not seeing the correct results

NOTE: When I just select the ‘Table Name’ option in the Data access mode in the oracle destination task. Source and destination tables datetime formats are exactly matching. But when I select ‘table name – fast load (using direct path)’ then I am seeing the datetime discrepancies. Since We have more than 70 millions records, I cannot use just the ‘table name’ option as it will take very long time to complete the data load.

Any suggestions on how I can get the valid date format using table name – fast load (using direct path) in the data flow task would be appreciated.

I have tried all the date type functions using derived column/ and data conversion but the data is not matching. I just wanted the date format should be matched between source table and the destination table along with the precision.