Scheduling Data Transformation Services?

Hi,
Our vendor created a series of dts local packages and I'd like to have them
run each night. When I right-click one of the packages and select Schedule
Package it comes up with a default schedule. So it looks like it is already
schedule, which is fine but how do I know if the package was run? Is this
activity tracked in a log somewhere? When I look under Package Log I see
it's empty. Any suggestions?
Thanks in advance,
LinnIn enterprize manager, go to
Management / SQL Server Agent / Jobs
From there you can view job history and edit the job itself.. as well as the
schedule.
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:uBL6RelMGHA.3432@.tk2msftngp13.phx.gbl...
> Hi,
> Our vendor created a series of dts local packages and I'd like to have
> them run each night. When I right-click one of the packages and select
> Schedule Package it comes up with a default schedule. So it looks like it
> is already schedule, which is fine but how do I know if the package was
> run? Is this activity tracked in a log somewhere? When I look under
> Package Log I see it's empty. Any suggestions?
> Thanks in advance,
> Linn
>|||First..I assume you are using ss2k.
If you open up the DTS package, and click Package, Proprties..and the
Logging tab, you will see in the bottom how to create an error file.
You could also set up a task to email yourself when the task completes
successfully(of course for this to work your server must be configured
to use Exchange.)
MJKulangara
http://sqladventures.blogspot.com

Scheduled DTS

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 'ImportFromMySQL'
(0xC89612CE034F6642BD585B048DBC0F06) - Status: Failed - Invoked on:
2005-06-08 10:05:02 - Message: The job failed. The Job was invoked by
Schedule 22 (ImportFromMySQL). The last step to run was step 1
(ImportFromMySQL).

Anybody know what's wrong!?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|||"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1118248290.928428.248230@.g44g2000cwa.googlegr oups.com...
> 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 'ImportFromMySQL'
> (0xC89612CE034F6642BD585B048DBC0F06) - Status: Failed - Invoked on:
> 2005-06-08 10:05:02 - Message: The job failed. The Job was invoked by
> Schedule 22 (ImportFromMySQL). The last step to run was step 1
> (ImportFromMySQL).
>
> Anybody know what's wrong!?

http://support.microsoft.com/defaul...kb;en-us;269074

Simon|||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 ;)|||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!?|||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.|||I am working directly on my server ...|||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.|||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!|||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\PNeumaier, 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\JSmith) 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/defaul...kb;en-us;269074

HTH,
Stu|||Peter Neumaier wrote:
> I scheduled a DTS-Import

> (Right-Click on the DTS package) it runs through without any problems.
> But firing it by a schedule doesn't work!?

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|||Peter Neumaier wrote:
> I scheduled a DTS-Import

> (Right-Click on the DTS package) it runs through without any problems.
> But firing it by a schedule doesn't work!?

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

Schedule backup with SQL 2005 Express

How do I schedule a backup with SQL Server 2005 Express. When I right-click a database and choose Tasks -> Back Up...
I don't get the option to schedule the backup. How do I do?

hi,

SQLExpress does not provide the SQL Server Agent, thus you can not schedule a "native" backup or whatever task via a SQL Server Agent job..

you have to rely on the native OS scheduler, writing your own command file including the actual BACKUP DATABASE... statement to be executed, or on other solutions even based on the OS scheduler, like http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, third party solutions like http://www.lazycoding.com/products.aspx or http://www.valesoftware.com/products-express-agent.php, http://www.codeproject.com/vb/net/SQLAgent.asp, or custom solutions based on the Service Broker as indicated in http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx

regards|||

Ok! Thanks.

Is SQL Server 2005 Express edition the only one that do not provide the SQL Server Agent?

|||

Thats right.

See the information from here:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

SQL Agent Job Scheduling Service

Jens K. Suessmeyer.

http://www.sqlserver2008.de

|||Excuse me, could you please tell me how can you backup your database use the Sql Express ?
I just want to use the osql -E order, but the system tell me that it fail to connect to the database, it may caused by the remote connections closed, but actually I allow the remote connection.
Is there another way to backup the database in the sql Express?
|||

The complete syntax for backup is proveded in Books Online, please search there for syntax help. If you are not being allowed to login, then you are either providing the wrong instance name or you are using an account that doesn't have permissions.

The most common error people make with SQL Express is that it is installed by default to an instance name, not the default instance, so you have to pass the entire instance name when connectiong:

osql -S <machine>\SQLEXPRESS -E ...

See if adding the instance name resolves your problem.

Mike

|||

If you are unfamiliar with the BACKUP syntax, you can use the UI command of SSMS(Express) and create a script which can be laterone copied to the AT job using the SQLCMD utility as Mike mentioned.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Schedule backup with SQL 2005 Express

How do I schedule a backup with SQL Server 2005 Express. When I right-click a database and choose Tasks -> Back Up...
I don't get the option to schedule the backup. How do I do?

hi,

SQLExpress does not provide the SQL Server Agent, thus you can not schedule a "native" backup or whatever task via a SQL Server Agent job..

you have to rely on the native OS scheduler, writing your own command file including the actual BACKUP DATABASE... statement to be executed, or on other solutions even based on the OS scheduler, like http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, third party solutions like http://www.lazycoding.com/products.aspx or http://www.valesoftware.com/products-express-agent.php, http://www.codeproject.com/vb/net/SQLAgent.asp, or custom solutions based on the Service Broker as indicated in http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx

regards|||

Ok! Thanks.

Is SQL Server 2005 Express edition the only one that do not provide the SQL Server Agent?

|||

Thats right.

See the information from here:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

SQL Agent Job Scheduling Service

Jens K. Suessmeyer.

http://www.sqlserver2008.de

|||Excuse me, could you please tell me how can you backup your database use the Sql Express ?
I just want to use the osql -E order, but the system tell me that it fail to connect to the database, it may caused by the remote connections closed, but actually I allow the remote connection.
Is there another way to backup the database in the sql Express?
|||

The complete syntax for backup is proveded in Books Online, please search there for syntax help. If you are not being allowed to login, then you are either providing the wrong instance name or you are using an account that doesn't have permissions.

The most common error people make with SQL Express is that it is installed by default to an instance name, not the default instance, so you have to pass the entire instance name when connectiong:

osql -S <machine>\SQLEXPRESS -E ...

See if adding the instance name resolves your problem.

Mike

|||

If you are unfamiliar with the BACKUP syntax, you can use the UI command of SSMS(Express) and create a script which can be laterone copied to the AT job using the SQLCMD utility as Mike mentioned.

Jens K. Suessmeyer

http://www.sqlserver2005.de