Hello everyone!

I have a DTS package that uses and ODBC DSN to tranfer data from my SQL DB
table to an AS400 table. When I run the package from within Enterprise
Manger (EM) or from the DOS command prompt (from a client or the SQL server)
the job executes fine. If I try to execute the package from within a sp
using the xp_cmdshell I get an error. Same thing happens when I place the
dtsrun command inside an Operating system command step within a job. Here
is the error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error at Destination for Row number 1. Errors encountered
so far in this task: 1.
Error source: DTS Data Pump
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Query cannot be updated because it contains no searchable
columns to use as a key.
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147213206 (8004206A)
Error string: The number of failing rows exceeds the maximum specified.
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0
Error Detail Records:
Error: -2147213206 (8004206A); Provider Error: 0 (0)
Error string: The number of failing rows exceeds the maximum specified.
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Query cannot be updated because it contains no searchable
columns to use as a key.
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL


Anyone know what I'm doing wrong here? Thanks in advance!

This may help :-

http://support.microsoft.com/?kbid=269074

--
HTH
Ryan Waight, MCDBA, MCSE

"Brian Watkins" <raistlin19 (AT) aol (DOT) com> wrote

Could be a blindingly obvious comment but do you need a primary key or
index on the table you're pushing the data into on the AS400 ?



Originally posted by Brian Watkins

Anyone know what I'm doing wrong here? Thanks in advance!



--
Posted via http://dbforums.com

Ryan,

Thanks for the link I think that it put me on the right track to solving
this one. I'll post to let you know what I did to fix it.

rcmburton,
It would seem blindingly obvious that I need a primary key if not for the
fact that the package only fails when scheduled in a job or used in a stored
procedure.
Since the package works when run manually the primary key error is
ridiculous. This is a package permissions/ownership issue. The error code
leave much to be desired in this case.