May 5, 2008 at 3:12 pm
I need to schedule a DTS package to run on the last day of the month and then the 1st-5th of every month. I haven't been successul in finding a way to do this with the wizard.
Is there a way to script this to create the jobs?
Or, any other easy solutions that I'm overlooking?
Thanx!
David
May 5, 2008 at 5:25 pm
It sounds like you're trying to schedule the package by right-clicking the package in Enterprise Manager and selecting "Schedule Package". That method allows you to create one schedule for the job so you should go ahead and create it with one of the schedules; the last day of the month for example. Then edit the resulting job and add new schedules. Actually, I think you'll need to create 5 new monthly schedules - one for each of the first five days of the month.
Alternatively, you can use sp_add_jobschedule and sp_attach_schedule to create the schedules and assign them to the existing job.
Greg
May 6, 2008 at 9:11 am
Thanx Greg!
That's what I was looking for!
David
May 6, 2008 at 10:25 am
You're welcome. It seems a lot of people forget that a job can have multiple schedules.:D
Greg
May 6, 2008 at 10:48 am
Yeah. What threw me was I tried to do all the scheduling at the same time.
Didn't occur to me to create each schedule seperately. D'oh! lol
Now, if I can just get this stored procedure to fire the DTS on demand, I'll be all set.
Thanx again!
David
May 6, 2008 at 4:13 pm
Here's how you can execute a DTS package from a stored procedure. Look up dtsrun utility in the Books Online for more information on the switches.
EXEC master.dbo.xp_cmdshell 'DTSRUN /S Name of server DTS package is on /N Name of DTS package /E'
May 6, 2008 at 4:40 pm
Thanx Erik.
I have the stored procedure figured out. Now it's a matter of getting it to execute from an ASP.Net page.
I keep getting a failure on Step 1 of the package when I attempt to run it from the webpage.
Otherwise, it executes perfectly. :ermm:
I just found this article.
I'm hoping it finally solves the issues for me.
http://www.codeproject.com/KB/vb/DTS__VBNET_.aspx
David
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply