December 16, 2011 at 1:33 pm
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
December 16, 2011 at 2:58 pm
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
December 16, 2011 at 3:15 pm
@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
01-JAN-0001 02.30.32.001001
01-JAN-0001 02.30.32.001001
01-JAN-0001 02.30.32.001001
Oracle table:
CREATE TABLE TESTTABLE(Date_value TIMESTAMP(6))
Thanks and Regards,
Praveena
December 16, 2011 at 3:19 pm
sorry the correct sample file values are
flatfilesample.txt:
01-JAN-0001 02.30.32.001001 PM
01-JAN-0001 02.30.32.001001 PM
01-JAN-0001 02.30.32.001001 PM
01-JAN-0001 02.30.32.001001 PM
December 22, 2011 at 1:51 pm
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy