Alternatives to Running Scheduling DTS Packages with SQLSERVERAGENT

We are using a product called Visual Cron to run SQL Jobs.  You can also run DTS Packages, SSIS Packages, Stored Procedures, and Queries.  It works much better then Windows Task Scheduler.  You can specify an account that you want to run each step of your visual cron job.

Can even run OSQL (or DTSRUN) jobs via a batch program which can be scheduled by Windows Scheduler. Also works for BCP if bulk loading data...

Is the problem with DTS - because you can write T-SQL code for anything DTS can do, or is it Scheduling out side of SQL Server ? Plenty of possibilities, but might need a bit more direction...

Visual Cron sounds promising as well - would be very handy to nominate a "secured" user account to run some of these types of things (is this the start of Unix invading MS ?)

Cheers,

Thanks for your suggestions so far, sorry for not being as active in this post as I should be...

Wiz, we cannot use the sqlserver agent because there was an issue previously and they will not allow us to use the agent with more permissions then it has now.  I'm not sure of the complete reason, that's just the gist.

Thor, if we can't get the dtsrun.exe to execute the scheduled task I will bring this up and see what they think about it, looks like a valid solution though. However, I think that the dtsrun will perform like we need it to. (I'll test it tomorrow)

Mark, it is a problem getting the DTS package to run under something other than sqlserver agent, I am interested in how to write T-SQL code for what the package executes (assuming the T-SQL code doesn't run the job in sqlserver agent).  Any learning resources I should look into for this specific purpose?

If you want to do it from OSQL you can also look in help for that command.  Since you already have the DTS package created dtsrun makes more sense.

There are several windows executable programs which can be launched fro the DOS prompt (using old fashioned term).

OSQL or DTSRUN (or BCP - and more) jobs can there be launched via a batch program which can be scheduled by Windows Scheduler. Also works for BCP if bulk loading data... They are considered part of the "engine" and so are available throughout every SQL server edition (except compact 3.5). In 2005 we also get sqlcmd which is meant to replace osql.

As a simple example for OSQL, which could also be DTSRUN as ThorSG1 shows above...

In windows, create a batch job (or cmd) : simple one liner, but would expect comments, versioning etc in "real life" e.g (between the rem's)
 
rem batch test to runs an osql script from dos
rem
"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" /E /e /n /ic:\mysql\jobs\UPDATE_RATE_CARD_END_DATE.sql /oc:\mysql\logs\UPDATE_RATE_CARD_END_DATE.log
rem
rem that is as easy as it can be
 
 
then simply create another text file, saving it with the extension sql (by way of convention) which has your T-SQL code...e.g. (between the -- )
 
--sql script to update the "end date" to be the end of day ie be a datetime finishing at 23:59:59
 
USE MYDB
go
IF (DB_NAME() <> 'MYDB')
BEGIN
  RAISERROR('Could not attach to MYDB database, aborting...',10,127)
  EXIT
END
go
 
select 'Started On : ',getdate()
go
 
update tbl_rate_card set end_time = dateadd(ss,-1,dateadd(dd,1,convert(datetime,convert(varchar(20),rca_end_time,106))))
GO
 
checkpoint
GO
 
select 'Ended On : ',getdate()
GO
 
-- that is all for now...
 
 
then all that has to happen is to run it - just like any other batch job. Or go to windows scheduler, new task, browse for the batch job, and schedule.

Select allOpen in new window

We had a problem coming up with the correct syntax that the dtsrun.bat should have.  To solve this issue we used dtsrunui.exe and that automated the process under advanced and automate.  I hope this helps anyone who is considering this option.  Thanks for the help everyone works like a charm.

Cheers

Thanks for the help!