DTS Schedcule Fails

[mfd777]

Running a job in DTS to pump data from Oracle to SMSQL7. Job works when manually executed. When you try to schedule, it does not work. Any known bugs? [sig][/sig]

 

[jcrr]

I'm experiencing the same problem and posted the problem in this forum, but haven't received a response. :-(

I suspect is a bug, too.

 

[wendyc]

Try this link out:

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

If you are using Enterprise Manager to connect remotely to another SQL Server, you won't be able to schedule the package to run on the server. It has to do with the way SQL Server handles logins and ownership of the DTS packages.

No bug.


[sig][/sig]

 

[Jimrv6]

What are the error message when you view the errors for the failed schedule item? I had a similar problem a few months ago.

Jim [sig][/sig]

 

[TomSark]

When you run the package as a job, the SQL Agent account is the account that is being used. You need to make sure that the agent account has the permissions needed to perform the steps in the package.

Tom [sig][/sig]

 

[jcrivera]

The error I'm getting is the following:

DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217887 (80040E21) Error string: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error source: Microsoft OLE DB Provider for SQL Server

I already checked that the account/password in both the MSSQLServer and SQLAgent services are the same and properly validated.

What else I'm missing?

J.C. [sig][/sig]

 

[TomSark]

Hi,

When scheduling DTS packages as jobs, you should follow this checklist:

** The package is owned by you
** Your account is a domain account being authenticated by a DC in, say,
DOMAIN2
** The SQL Server and Access database reside in another domain (DOMAIN2), on
different servers
** The SQLAgent is being run under the context of a domain account
** The SQLAgent account and your account are both members of
BUILTIN\administrators on the SQL Server
** Are those accounts members of BUILTIN\administrators on the server with
the Access database?
** The SQLAgent account and your domain account are members of the sysadmin
fixed server role
** The directory in which the Access database resides is shared, with
"Change" and "Read" permissions granted to BUILTIN\administrators
** The SQLAgent account and your domain account have been permitted access
to the SQL Server database
** These users have not been explicitly DENIED access to any object being
"touched" by the DTS package in the database.

Thanks,

Tom