Script to automatically schedule SSIS tasks?

Hi,

I have a SQL 2000 script which I use to automatically schedule various backup tasks. This script adds and schedules a full backup once a week, differential backups nightly, and log backups hourly, in addition to a couple other maintenance tasks such as rebuilding indexes.

The idea is that a less technically savvy person can set a few variables at the top of the script (such as DB name and backup file folders) and click 'execute' to run the script and schedule the backups etc all in one go, for different clients.

Since some of the stored procs I use in this script are deprecated in SQL 2005, I am trying to replicate this functionality in SSIS but am having trouble figuring out how I can get all of this functionality encapsulated in the same 'click and go' manner where the user can simply execute the package and all the jobs will be scheduled without any user interaction.

Is this even possible? Where should I be looking for examples of how to do this?

Thanks!

dtexecui.exe is the closest thing there is to a "click-and-go" interface. I suggest you evaluate that.

-Jamie

|||Yes, I was assuming that's how I would run the package, but what I am trying to figure out is how to set up scheduling of the different jobs within the package so that when I run it, they are all scheduled at the correct times (like one job is scheduled for nightly, one for weekly, etc etc).

There does not seem to be a 'scheduler' task in the toolbox of VS.
|||

graemeo wrote:

Yes, I was assuming that's how I would run the package, but what I am trying to figure out is how to set up scheduling of the different jobs within the package so that when I run it, they are all scheduled at the correct times (like one job is scheduled for nightly, one for weekly, etc etc).

There does not seem to be a 'scheduler' task in the toolbox of VS.

If you want to use a scheduler then use SQL Server Agent. There is no scheduler within a SSIS package and nor should there be.

-Jamie

|||Is there any way to do what I am attempting (provide the user with a simple script to schedule different backup jobs at varying times?) in SQL 2005 without using deprecated SP's?
|||

graemeo wrote:

Is there any way to do what I am attempting (provide the user with a simple script to schedule different backup jobs at varying times?) in SQL 2005 without using deprecated SP's?

Yes. This is a question about SQL Server agent (i.e. the scheduler). There are a bunch of sprocs dedicated to maintenance of SQL Server Agent jobs.

http://search.live.com/results.aspx?q=agent+stored+procedures&form=QBRE&q1=macro%3Asql_server_user_education.booksonline

-Jamie