I have read to my eyes are blurry but I can't seem to find any one post that addresses my issue. I have a dts package that I can successfully execute manually that pulls a table from the AS400 to a sql server table: however, if I schedule a job to run the same package and start it then the job always fails. I get the following error message:
Executed as user: MyComputer\SYSTEM. ...un: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_
2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_
1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: CWBSY0002 - Password for user X on server AS400 is not correct Error source: IBMDA400 Session Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 8002 (1F42) Error string: CWBSY0002 - Password for user X on server AS400 is not correct Error source: IBMDA400 Session Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_
3 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
When I looked at my one connection to the AS400 in the DTS package, I see the following checked below my userid - blank password and save password. I uncheck blank password and re-save the package after entering my password again. I execute the package successfully and save the changes then I go to sql server agent and run the job. I get the same error message as above. I have done this numerous times. Next, I tried setting up a DSN connection on my computer and using it in my DTS package instead of the iSeries Access UDB2 provider. It errors out as well. It gives me an unspecified sql error and will not even pull my table from the AS400 in the package.
I set up a linked server to my AS400 and was able to pull the table data into query analyzer and view it.
I am stumped. I know there is a bug in DTS import wizard. See KB: http://support.microsoft.com/kb/315994
It's the error message I received when tried to use the import wizard.
However, another sql developer in the building has used DTS on his machine successfully to run AS400 packages through a DSN connection.
Has anybody else run into this issue?