January 11, 2009 at 10:27 pm
HI,
how to include 2 SSIS packages in single job with 2 different schedule??
SSIS package1 should run daily.
SSIS package2 should run monthly.
how do i implement both the packages in a single sql job with 2 different schedules for each package.???
TIA,
January 12, 2009 at 1:05 am
I don't think that is possible using different schedules for different packages, the solution that comes to my mind is something like this:
1. Create a table in your DB(MontlyPackageStatus) with 1 columns (LastExecutedMonth datetime).
2. Create a job with type SQL Command.
3. The job script will be something like this:
xp_cmdShell 'DTEXEC YourDailyPackage'
DECLARE @maxDate datetime
SELECT @maxDate = max(LastExecutedMonth)
FROM MontlyPackageStatus
IF (@maxDate = DATEADD(dd,-(DAY(getdate())-1),getdate()))
--Compare max date with first day of current month (check below code for inserting the metadata)
BEGIN
xp_cmdShell 'DTEXEC YourMonthlyPackage'
INSERT INTO MontlyPackageStatus
VALUES (DATEADD(dd,-(DAY(@mydate)-1),@mydate)) --Insert first day of current month
END
4. Create a schedule to execute the job daily.
The above job will execute daily and the package that you want to execute daily will always be executed, but your monthly package will excute only once in a month.
-Vikas Bindra
January 12, 2009 at 10:03 am
I would suggest
create 1 Maint Plan with 2 subplans
Then you could attach a different schedule to each subplan
I do it for my
FULL backup weekly
DIFF backup daily
TLOG backup hourly
Backup file clean up weekly
.....
or it seems like you can create 2 STEPS in 1 SQL job, just put in "Maintenance Plans\PlanName" in the package name
Although it seems like you cannot use separate schedule per separate step. I just tried it in SQL 2005
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply