Executing a DTS package as a scheduled job 1

[shelley1]

I have created a DTS package which transfers data from the AS400 to SQL Server. This executes fine on the server but when I run it as a scheduled job it fails. I have found lots of articles dealing with this problem but I still don't seem able to get it to work.

 

[tlbroadbent]

Jobs almost always fail because of permissions. When executed from EM by a user, a job runs with that user's permissions. When scheduled, it is run by the SQL Agent with the SQL Agent account.

Have you looked at the Job detail in Job History to see why it fails? Or are you not even getting an entry in Job History?

Who owns the Job? Open Job properties to check. Change the owner to a system administrator if it isn't currently owned by anadministrator.

What login is used to start the SL Agent? Does that login have permissions to access the data on the AS400? If not, it must be granted permissions. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[shelley1]

Initially I did seem to have a permissions problem, I could run the package from EM on my machine but it wouldn't run on the SQL Server. I managed to get round this. I can execute the package from EM on the SQL Server even though the login used by the SQL Server has no permissions to the 400. I am using an ODBC connection to the 400 with a 400 login set up on the connection.

I have tried changing the job owner to the system adminstrator but this didn't work.

I am getting some job history which I have attached for further info

... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217887 (80040E21) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Copy Data from SALEDATA to [AS400Data].[dbo].[SALEDATA] Step DTSRun OnError: Copy Data from SALEDATA to [AS400Data].[dbo].[SALEDATA] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80... Process Exit Code 2. The step failed.

I have looked up these error messages but don't seem to be getting any joy.

The version of SQL is 7.0 sp2.

 

[tlbroadbent]

Was the ODBC data source created as a User or System data source? If it is a user data source, change it to a system data source.

Is there any chance that the tools such as EM are at SP2 level while the server is still on SP1? Jobs created or modified by SP2 EM will not run on a SP1 SQL Server installtion.

You said you had read the recommended articles on this problem. Did your reading include these articles?

http://www.swynk.com/friends/green/dtstop5.asp

http://support.microsoft.com/support/kb/articles/Q269/0/74.ASP

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[shelley1]

The ODBC DSN was already set up as a system DSN. SP looked fine.

I read the first article that you suggested and used the ScriptPkg which comes with SQL 7.

I used this to script my packages and then ran the executables using task manager, it worked. I'm still hoping to get the job scheduler to work with the 400 but I'm okay for now.

Thanks A Lot Shelley.

 

[Guest_imported]

Hi,

I would like to transfer data from Oracle 8 to SQL Server 2000. Please tell me what are the consistent, reliable ways of transfering data other than DTS

thanx in advance

Ruban

 

[tlbroadbent]

Ruban,

Please post your question in a new thread. More people will look at a new thread than at the end of an old thread. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin