Loading TIMESTAMP(6) column in oracle 10g using SSIS from a flat file

Question

Loading TIMESTAMP(6) column in oracle 10g using SSIS from a flat file

venus.pvr

SSCarpal Tunnel

Points: 4813
More actions
December 16, 2011 at 1:33 pm

#253666

Hello everyone,
I am loading data into an oracle table from a flat file using ssis. The destination oracle_table has a TIMESTAMP(6) column which stores date to more granular level (for ex: 01-JAN-0001 02.30.32.001001 PM). when I use the oracle_table as a destination, I get an error "Datatype is not supported" at the destination because of the TIMESTAMP(6) column. So, instead of using table as a destination, I used SQL command SELECT CAST(DATE_VALUE AS VARCHAR2(30)) DATE_VALUE FROM oracle_table. This caused a runtime error inturn with the date column as below:
Error: 0xC020901C at Data Flow Task 1, Destination - BOMComponentSample [82]: There was an error with input column "DATE_VALUE" (356) on input "Destination Input" (95). The column status returned was: "The value violated the schema's constraint for the column."
Please let me know if anyone knows of a way to deal with TIMESTAMP(6) datatype without losing any millseconds data.
Thanks and Regards,
praveena

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think you want to use the TO_CHAR function on the oracle side...something like this, depending ont he desired format:(what is the timestamp(6) expected value? does it include TIME?)

SELECT TO_CHAR(DATE_VALUE,'YYYYMMDD') AS DATE_VALUE FROM oracle_table

--OR

SELECT TO_CHAR(DATE_VALUE,'YYMMDD') AS DATE_VALUE FROM oracle_table

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

venus.pvr SSCarpal Tunnel Points: 4813 More actions · Answer 2

@lowell : The timestamp(6) expects a value in the format '01-JAN-0001 02.30.32.001001 PM'. I used the same format in the flat file source.

Below is the test data you can use to test:

flatfilesample.txt:

Date_value

01-JAN-0001 02.30.32.001001

Oracle table:

CREATE TABLE TESTTABLE(Date_value TIMESTAMP(6))

Thanks and Regards,

Praveena

venus.pvr SSCarpal Tunnel Points: 4813 More actions · Answer 3

sorry the correct sample file values are

flatfilesample.txt:

01-JAN-0001 02.30.32.001001 PM

Dave23 Hall of Fame Points: 3903 More actions · Answer 4

In your SSIS dataflow, you could try using a Data Conversion Transformation, and convert the outbound flat file date column into a [DT_DBTIMESTAMP2] with a scale of 6. That data type is the SSIS equivalent of the Oracle TIMESTAMP(6) data type.