DTS runs fine from Enterprise Manager, fail when scheduled - ORACLE problem

Question

DTS runs fine from Enterprise Manager, fail when scheduled - ORACLE problem

riska

Valued Member

Points: 54
More actions
January 22, 2004 at 1:50 am

#65025

I pull the data from ORACLE server to my table in MS SQL server. When I start the DTS from Enterprise Manager it works but when I schedule this package it fails. Another tasks in the package are running.
I found in log file this message:
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (8004202b): TransformCopy 'DTSTransformation__15' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'column1' (DBTYPE_VARNUMERIC), destination column 'column1' (DBTYPE_R8)).)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
In Oracle column1 is datatype number
in SQL Server column1 is datatype float
I tried to use in query round(column1, 3) but doesn't help.
Thanks for help.

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Keep in mind that when you run DTS from Enterprise Manager, the DTS package is actually executing on your workstation, not on SQL Server. When you schedule it to run, it runs as a SQL Server Agent job on the server.

As to the exact issue with the datatype conflict, I'm not an Oracle person, hopefully one will see this and know the cause.

K. Brian Kelley
@kbriankelley

riska Valued Member Points: 54 More actions · Answer 2

Thanks bkelley for answer. I know this but DTS works when I use the Enterprise Manager in Terminal services directly on the server. Then the DTS should use the same data providers as scheduled.

jordanac Hall of Fame Points: 3653 More actions · Answer 3

Hi,

One of the causes for this is that the Oracle datatype number can have a very large precision data type, that is too large for vbscript to handle. (Its actuly an ADO limitation)

When this happens I normaly change my SQL Server column to nvarchar or do a select * into test.

When you see that the select into has created a column of nvarchar for the Oracle number column the precision is too large for the script.

Andy.

Tobi White Mr or Mrs. 500 Points: 551 More actions · Answer 4

I agree with jordanac. I suggest pumping the oracle numeric data into a SQL varchar column. Then you can simply change the type of that column to float if it is indeed the issue of the precision being to great for ado.