Scheduled DTS

[Peter Neumaier]

Hi!

I scheduled a DTS-Import from MySQL, whenever I run it manually
(Right-Click on the DTS package) it runs through without any problems.

But firing it by a schedule doesn't work!?

Just to exclude any issues regarding users/roles, I created a DTS to
export files to my desktop to an EXCEL-sheet. Manually export as well
as scheduled export works fine.

My Application Log shows me following error message:

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 6/8/2005
Time: 10:05:02 AM
User: N/A
Computer: *****
Description:
SQL Server Scheduled Job 'ImportFromMySQ L'
(0xC89612CE034F 6642BD585B048DB C0F06) - Status: Failed - Invoked on:
2005-06-08 10:05:02 - Message: The job failed. The Job was invoked by
Schedule 22 (ImportFromMySQ L). The last step to run was step 1
(ImportFromMySQ L).


Anybody know what's wrong!?

[Stu]

Re: Scheduled DTS

Be sure that the account under which the job is running has the same
permissions to retrieve and export as your user account does. When you
run a DTS package manually, you are running it under the context of
your username and the machine on which you are currently running
Enterprise Manager (ie, you use your workstation to manage a server).
When the job is scheduled, it is running under the context of the
account by which SQL Server is running, as well as from the perspective
of the server.

HTH,
Stu

[Simon Hayes]

Re: Scheduled DTS


"Peter Neumaier" <Peter.Neumaier @gmail.com> wrote in message
news:1118248290 .928428.248230@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> Hi!
>
> I scheduled a DTS-Import from MySQL, whenever I run it manually
> (Right-Click on the DTS package) it runs through without any problems.
>
> But firing it by a schedule doesn't work!?
>
> Just to exclude any issues regarding users/roles, I created a DTS to
> export files to my desktop to an EXCEL-sheet. Manually export as well
> as scheduled export works fine.
>
> My Application Log shows me following error message:
>
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 6/8/2005
> Time: 10:05:02 AM
> User: N/A
> Computer: *****
> Description:
> SQL Server Scheduled Job 'ImportFromMySQ L'
> (0xC89612CE034F 6642BD585B048DB C0F06) - Status: Failed - Invoked on:
> 2005-06-08 10:05:02 - Message: The job failed. The Job was invoked by
> Schedule 22 (ImportFromMySQ L). The last step to run was step 1
> (ImportFromMySQ L).
>
>
> Anybody know what's wrong!?
>[/color]

http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

Simon

[getinked]

Re: Scheduled DTS

are you connected in the Enterprise Manager to your local machine and
trying to run the package that is saved on the server? if so run the
package by remoting in to the server and running it from there.

basically what STU said in lamans terms ;)

[Peter Neumaier]

Re: Scheduled DTS

Thank for the answer.

"Just to exclude any issues regarding users/roles, I created a DTS to
export files to my desktop to an EXCEL-sheet. Manually export as well
as scheduled export works fine. "

So there seems not to be an problem with the security, I build a second
DTS package,
that exports data to an excel file, with the same login (by the way I
am working physically
on the server, always with the same login) and this second DTS schedule
works ...

But my scheduled MySQL-Import-DTS makes trouble!?

[Stu]

Re: Scheduled DTS

Just to clarify:

Running the DTS packagage manually runs it under the account you logged
in as; running it by the job scheduler runs it under the account used
by the SQLAgent. The default setup for SQL Server assigns a system
account to the SQL Agent, which may not have the appropriate
permissions on the MySQL server.

Verify that the SQLAgent account (the account listed under the Windows
Services listing) has permission to read data from the MySQL database.

[Peter Neumaier]

Re: Scheduled DTS

I am working directly on my server ....

[getinked]

Re: Scheduled DTS

do you have a drop table at the beginning of your package.

1st step should be drop table 'tablename'
2nd step create table
3rd connection
4th export to file.

when you try to export a job to excel you gotta make sure to drop the
worksheet you are exporting to before you can create a new one. i'm
not sure if that helps but to throw it out there.

[Peter Neumaier]

Re: Scheduled DTS

getinked,

the example I mentioned with the EXCEL-file is just to show that
scheduled DTS packages are working (only this export to excel).

But my scheduled import from MySQL doesnt start up!

[Stu]

Re: Scheduled DTS

Are you logging in using the same account as the SQLAgent? I'm not
sure if I'm communicating that clearly; I understand that you are
logging on to the server directly, but are you logging on as the same
account as the SQLAgent? You can see what account the SQLAgent is
running under by going to the Services section of the Administrative
Tools .

Unless you specified a different account for the SQL Agent when you set
up SQL Server, it is probably running as a Local System Acccount, which
would probably NOT have the rights to reach your MySQL Server
(especially if its on a different server). It WOULD, however, have the
rights to read data from the local SQL Server and write it to a local
Excel file.

In most cases, the SQL Agent should run under a named domain account
(e.g., MyDomain\Jsmith ) in order for the Agent to have rights on the
Windows network. If you log on to the box as MyDomain\PNeuma ier, you
can run a DTS package manually, and it will run as you. If you
schedule it, it's as if the SQL Agent (MyDomain\JSmit h) is running it,
and that account may not have the same priviliges as you.

Is that clearer? I don't mean to sound as if I'm beating a dead horse
here, but sometimes text-based communication is difficult to use to
explain a process.

If you are logging on as the same account (or the SQLAgent account has
identical priviliges), then I'm at a loss at this point. However,
Simon's link from above is a great resource for explaining this issue:

http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

HTH,
Stu

[Jim Andersen]

Re: Scheduled DTS

Peter Neumaier wrote:[color=blue]
> I scheduled a DTS-Import[/color]
[color=blue]
> (Right-Click on the DTS package) it runs through without any problems.
>
> But firing it by a schedule doesn't work!?[/color]

I had a similar problem. Turned out I told it to make a log file (on the
desktop or something), and it couldn't access that when running as
scheduled. Changed it to c:\logfile.txt instead. That solved it.

/jim

[Jim Andersen]

Re: Scheduled DTS

Peter Neumaier wrote:[color=blue]
> I scheduled a DTS-Import[/color]
[color=blue]
> (Right-Click on the DTS package) it runs through without any problems.
>
> But firing it by a schedule doesn't work!?[/color]

I had a similar problem. Turned out I told it to make a log file (on the
desktop or something), and it couldn't access that when running as
scheduled. Changed it to c:\logfile.txt instead. That solved it.

/jim