Scheduled Job works when run manually but not when scheduled

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:
"SQL Server Scheduled Job 'Update SAGE
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
ColinHi 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
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1175249472.172771.158350@.y80g2000hsf.googlegroups.com...
> 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:
> "SQL Server Scheduled Job 'Update SAGE
> 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
>|||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"
<bobby2@.blueyonder.co.uk> wrote:
>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:
>"SQL Server Scheduled Job 'Update SAGE
>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|||On 30 Mar, 12:52, Sue Hoegemeier <S...@.nomail.please> wrote:
> Check the following knowledge base article for information
> on troubleshooting this issue:
> INF: How to Run a DTS Package as a Scheduled Jobhttp://support.microsoft.com/?id=269074
> -Sue
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|||> 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.
>
Hmmm... Not quite as simple as I thought. Anybody know how to install
the SAGE ODBC driver without installing the whole software package?
Colin|||On 30 Mar, 13:58, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> > 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.
> Hmmm... Not quite as simple as I thought. Anybody know how to install
> the SAGE ODBC driver without installing the whole software package?
> Colin
Ok, forget it - I've worked it out
Colin