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

Script tasks not pre-compiling all of a sudden

I've been building and running script tasks for years without issue. Then all of sudden last week Visual Studio starts showing the "Task is configured to pre-compile the script, but binary code is not found" error anytime I open a script -- even I make no changes. If I copy the dtsx package to another machine, the script compiles fine and I can see the binary data in the raw .dtx file -- so I know the code is correct.

I can also reproduce the error simply by adding a new script task, going into "Design Script", make no changes to the default code (which is basically one line: Dts.TaskResult = Dts.Results.Success) and simply press "OK" on the Script Task Editor.

I've been trying to find some VS setting somewhere that might stop the script IDE from producing the binary code, but I can't. It doesn't seem to be project setting, because all my SSIS projects are now suffering this problem on my main dev box.

Anyone experience this?

Did you just recently apply a bunch of Microsoft patches?

Did you see this thread? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

Script tasks don't work in Production

Hallo,

In SSIS Version 9.00.2047.00 I have build a few packages using script tasks. These packages work well in development.

When I try to run them Using DTUtil the systems displays in the Package Excecution Progress-window the message: 'Error: The tasks "reading registry" cannot run on this edition of Integration services. It requires a higher level edition.'

Emptying the script task from its variables and script doesn't make a difference. I did not find an explanation in BOL. Concerning script tasks I only find

The Script task uses VSA as its engine for writing and running scripts. To run a script, you must have VSA installed on the computers where the package runs. But since I even get these messages when I try to run on the machine where I build the package,I guess this can't be the problem.

Any ideas?

Kind regards,

Paul

Check out the solutions by Daniel Read @. http://www.developerdotstar.com/community/node/312

Thanks,
Loonysan

|||It looks like you only installed Tools component on Production server. You need to install SSIS component (a separate checkbox during Setup and different licensing requirements) to run packages stand-alone (outside of the designer). Actually, you may not need to Tools component on the production server at all - Tools are needed to design and debug packages, but for execution the SSIS server component should be enough.|||

Hi Michael,

Thanks for your swift reply.

I admit i'm not a SQL server specilist but while installing SQL Server 2005 from CD (Microsoft SQL Server 2005 Enterprise Edition December 2005) or DVD the option 'Integration services' is disabled. So I can not single it out for installation. . Is there still an other installation dvd I need or can I download the SSIS server component from somewhere? I did not seem to find it in the download center

With kind regards,

Paul

|||What is the OS on the production machine? Server components in Enterprise Edition can only be installed on Server operating system, not on Windows XP or Windows 2000 workstation. For production server, use server OS like Windows 2003 Server.