We help IT Professionals succeed at work.

Problem loading the oracle table with Timestamp (2)with local timezone data type which is not supported in sql server

I have created a ssis package to load the csv file to oracle destination table.
I have a Destination column with a datatype Timestamp(2) with local timezone in oracle table.

My flat file (csv) column value will look like these:  "3/1/2011 1:38:32 PM" . I need to provide the datatype manually for the source in the advanced tab of flatfile conn manager to the columns looking at the destination table metadata.

I am using Oracle destination  (added as a dataflow item from codeplex) as the oledb destination is not permitting this type of datatype as it says datatype is not supported.

So how can I populate the value. I haven't faced the situation before.

When I use database timestamp(2) with timezone in the source connection manager, it is not matching when it comes to destination.

Could you please guide me.
Comment
Watch Question

Top Expert 2011

Commented:
- a quick suggestion is to create a temp table, similar to the actual table. change the timestamp datatype to char or varchar then load the data to the temp table. then once loaded, you can do insert statement to the actual table and use to_timestamp function to format the date to the desired format.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm
Top Expert 2011

Commented:
- i might misunderstand your question, is it load from oracle to sql server? then u can still use a temp table in sql server as staging with char or varchar datatype. then load data from oracle to the temp table, then from temp to actual table by formatting the timestamp in sql server itself.

Author

Commented:
Thanks for quck reply.

How can i create a temp table as  I am loading from flat file to oracle table. Its just direct loading of the data from flat file to oracle table.
Top Expert 2011

Commented:
- i'm on mobile so very limited instruction that i can share.
1- create a same table structure to be the staging table like the actual oracle table. either using create table command - "create table tempB as select * from tableA" or the usual create table ddl. then in tempB table, change the datatype of the problem column to varchar/char.
2- next modify your load script to insert the flat file into the tempB table. in your script, remove the timestamp datatype if it is defined in there. define it as varchar/char.
3- once loaded into tempB, write an insert statement from tempB to tableA (your actual table) and use either to_date to to_timestamp function on that problem column.

Author

Commented:
this data is loaded into the staging tables I can't create another table so no question of creating another temp table...
Top Expert 2011

Commented:
- are you using sqlloader? can you share the control file you use to load the data into oracle? sample data and the staging table ddl would also help us to assist you further.

Author

Commented:
no i am using SSIS (ETL tool)  as I have mentioned in the description


CREATE TABLE "SPSTAGING"."SP_GLOBAL_STG"
(
"IFNUMBER" NUMBER(10,0),
      "IFSTACKLASTCHANGE" TIMESTAMP (2) WITH LOCAL TIME ZONE,
      "IPV6IPDEFAULTHOPLIMIT" NUMBER(10,0),
      "IPV4INTERFACETABLELASTCHANGE" TIMESTAMP (2) WITH LOCAL TIME ZONE,
      "IPV6INTERFACETABLELASTCHANGE" TIMESTAMP (2) WITH LOCAL TIME ZONE)  


 I am enclosing the sample data as well.

Thanks for the replies guys
BridgeGlbl.csv
Top Expert 2011
Commented:
- sorry that i miss SSIS in your question. i've done the following test in oracle by creating a table with a column call tesdate with timestamp(2) with local time zone:

SQL> create table test (testdate timestamp(2) with local time zone);
Table created.

-- insert sample data based on oracle date
SQL> insert into test (testdate) select sysdate from dual;
1 row created.

SQL> select * from test;
TESTDATE
---------------------------------
05-DEC-11 04.43.18.00 PM

-- i take one of the date from your flat file and using to_date() function to format the date:
SQL> insert into test (testdate) select to_date('3/1/2011 13:38','dd/mm/yyyy hh24:mi') from dual;
1 row created.

SQL> select * from test;
TESTDATE
----------------------------------
05-DEC-11 04.43.18.00 PM
03-JAN-11 01.38.00.00 PM

- as you can see, the date and time is inserted. in you SSIS script, is it possible to specify to_date() function so that Oracle would accept the data?