SQL Server - Scheduled Job works when run manually but not when scheduled

Asked By Bobby on 30-Mar-07 05:11 AM
Hi
I have a very simple local package, which performs the following:

1. Execute SQL Task
Delete from PURCHASE_LEDGER
Delete from SALES_LEDGER

2. Transform Data task
Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER

There is a workflow between steps 1 and 2, so that step 2 only
executes if step 1 is successfull.

The connection to SAGE is ODBC (which I know works fine) and to SQL it
is OLE.

This package works perfectly if I right click on it and select
execute. However, if I schedule it, the first step works perfectly but
the second step fails. I know that the first step works because the
tables are empty. If I remove the first step and the workflow from the
package, the transform data task still does not work when scheduled,
but is fine when I run it manually. In the error log, it just says:

Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
Invoked on: 2007-03-30 05:00:00 - Message: The job failed.  The Job
was invoked by Schedule 6 (Update SAGE Tables).  The last step to run
was step 1 (Update SAGE Tables)."

Any idea why this package should work manually but not when scheduled?

Thanks

Colin




Barry Andrew Hall replied on 30-Mar-07 05:53 AM
Hi Colin,

Are the accounts you use to 1) execut the package manually and 2) the
account that runs it via a schedule, the same account?

Without digging too deeply to me it sounds like the account that is used for
the scheduled job, isnt the same as the one where you execute it manually.
Barry
Sue Hoegemeier replied on 30-Mar-07 06:52 AM
Check the following knowledge base article for information
on troubleshooting this issue:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074

-Sue

On 30 Mar 2007 03:11:12 -0700, "Bobby"
Bobby replied on 30-Mar-07 07:13 AM
Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
It's set up on my p/c, which is why it works manually, but when it's
scheduled it runs from the server and can't find the link to SAGE.

Thanks

Colin
Bobby replied on 30-Mar-07 07:58 AM
Hmmm.... Not quite as simple as I thought. Anybody know how to install
the SAGE ODBC driver without installing the whole software package?

Colin
Bobby replied on 30-Mar-07 08:30 AM
Ok, forget it - I have worked it out

Colin