SSIS - ORA-01722 - Invalid Number-CodePudding

I makeing simple SSIS dtsx pack where I select data from Sybase DB and I want to insert them to Oracle DB. Unfortunettly, I am getting ORA-01722 error code, but I cant figure out where I made a mistake.

First table (Sybase DB):

select * from d_agent

Data type for first table:

NUMERIC,NUMERIC,DECIMAL,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,TIMESTAMP,INTEGER

Data that cant be inserted and error ORA-1722: 2,5005000,NULL,NULL,NULL,NULL,NULL,NULL,22436705,Test,Jun 30 2005, 96

Second table (Oracle DB):

ODBC Destination and right table selected copy.d_agent_sez

Data type for second table:

NUMBER,NUMBER,NUMBER,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,DATE,NUMBER

SSIS error msg: [Transfer_ORACLE] Error: Open Database Connectivity (ODBC) error occurred. state: '22018'. Native Error Code: 1722. [Oracle][ODBC][Ora]ORA-01722: invalid number

What am I missing? Please help :) Thank you for help.

CodePudding user response：

First problem is to convert timestamp to date >> I think this will not be possible...

Oracle:

For your column with date datatype:

create table t1(a date)

insert into t1 values(Jun 30 2005) 
--this will not work
insert into t1 values(to_date('Jun 30 2005', 'Mon DD YYYY'))
--this will work

For your column with varchar datatype:

create table t2(b varchar(100))
insert into t2 values(Test)
 --this will not work
insert into t2 values('Test')
--this will work

When I correct this two things insert is ok... Here is a demo