Re: Error running DTS package on a schedule (only!)


"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote in message
news:A38F5285-9162-4C98-AADD-6F59800BFFCF@xxxxxxxxxxxxxxxx
|I created a DTS package & CAN run it (successfully) from Enterprise Manager
| and from a command prompt.
|
| When I try to schedule a job for this DTS package under SQL Server Agent
| Jobs it gets the following error:
|
| ATTRIBUTE_TYPES to [ThankYouAcknowledgements].[dbo].[ATTRIBUTE_TYPES] Step
| DTSRun OnError: Copy Data from ATTRIBUTE_TYPES to
| [ThankYouAcknowledgements].[dbo].[ATTRIBUTE_TYPES] Step, Error =
| -2147467259 (80004005) Error string: [Sybase][ODBC Driver]Unable to
| connect to database server: specified database not found Error
source:
| Microsoft OLE DB Provider for ODBC Drivers Help file: Help
| context: 0 Error Detail Records: Error: -2147467259
(80004005);
| Provider Error: 83 (53) Error string: [Sybase][ODBC Driver]Unable
to
| conn. The step failed.
|
| Why does this package get an error ONLY when it's a scheduled job?
|
| Thanks in advance.
| --
| Tyler


Hi,

This is a problem of security context

- If the package is run from a command prompt by using DTSRun.exe or from
DTS Designer, the credentials of the currently logged-in Windows NT account
is used
- If the package is run as a SQL Server Agent job, then the integrated
security connection is made using the account you used to start SQL Agent

Notes :
if the SQL Server was started using the Local System account, the DTS
package has no permissions outside of the computer that is running SQL
Server.

SQL Server Agent account must be a sysadmin group member.

So, your schedule package fails because the account you use to start SQL
Server Agent has NOT enought rights to make a connection to your database
server from your ODBC DSN

Hope it's help you
------
Philip


.

Relevant Pages

  • Cross post recommended on this seemingly sinple problem
    ... >I have an ActiveX Script task in a DTS package which executes fine ... Why does the scheduled job not execute ... > schedule and the account is a domain account with windows ... even with both the package and the job owned by the same account (I ...
    (microsoft.public.sqlserver.dts)
  • Re: NEWBIE - cant run SSIS package in SQL server agent scheduled job?
    ... The package will run if I log into the server under the given account and right click on the job and chose "run". ... I added a "step" to the job that ran an operating system command to map the necessary drive prior to running the main package and it worked. ... The connection uses a SYSTEM DSN connection to get data from another source on another machine. ... the SQL Server Agent service is also set up to run under account "x". ...
    (microsoft.public.sqlserver.dts)
  • RE: SSIS Problem
    ... If SQL Agent is running under Local Account ... When I execute the package in the Execute Package ... when I run this package under the SQL Server agent (the step ...
    (microsoft.public.sqlserver.tools)
  • RE: Date Format not working
    ... Compare the OS date format for the user who runs the package from ... Enterprise Manager and the NT account that SQL Server Agent runs under. ...
    (microsoft.public.sqlserver.dts)
  • Re: Jobs Failed do not work when scheduled done via DTS Scheduled Package
    ... we created a very simple DTS package like reading the last entry ... Of course the DTS local package works and FAILS if we schedule it. ... pretty much a "god" like account. ... > Is that the exact error message you get for all of the jobs ...
    (microsoft.public.sqlserver.server)