Package fails when it is scheduled

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bobby - 06 Mar 2008 22:52 GMT

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?

Thanks

Colin

Reply to this Message

schal - 06 Mar 2008 23:56 GMT

Most probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")

> Hi
> I'm using SQL Server 2000. I've created a local package which runs
[quoted text clipped - 5 lines]
>
> Colin

Reply to this Message

Bobby - 08 Mar 2008 09:44 GMT

> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")

The history log says the following:

Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.

The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.

The package works fine if I

Reply to this Message

Bobby - 08 Mar 2008 09:46 GMT

> > Most probably, its the permissions given to the userid that is used to
> > call the job.
[quoted text clipped - 29 lines]
>
> The package works fine if I

......right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!

Thanks for any help,

Colin

Reply to this Message