SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Import Main Plan Pkg and schedule it with scripts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mciancia
Starting Member

2 Posts

Posted - 03/09/2013 :  00:06:54  Show Profile  Reply with Quote
In SQL Server 2008 R2, I created a Maintenance Plan with the GUI. I then exported the Plan out. I need to import this package and schedule it on multiple servers, so I want to script it as much as possible. I did find a way to import the package from the command line:

dtutil /Quiet /FILE C:\temp\MaintenancePlanTest.dtsx /COPY SQL;"Maintenance Plans\MaintenancePlanTest"

But this doesn't schedule the plan so the job doesn't get created. Is there a way to script setting up the schedule and job for the plan?

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/09/2013 :  01:08:01  Show Profile  Reply with Quote
yep...you need to script out job and schedule from SQL server agent. connect to server from SSMS expand server-> SQL ServerAgent. Expand jobs and you'll be able to see job corresponding to your maintenance plan. You can script out job from it by rightclicking and choosing option Script Job AS -> CREATE -> New Query Window.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mciancia
Starting Member

2 Posts

Posted - 03/09/2013 :  11:15:12  Show Profile  Reply with Quote
I imported the plan and then manually set the schedule/job. I then did what you said to get the script. I then removed the maintenance plan which also removed the job. I then imported the plan again, and then ran the script, which did create the job. However, when I ran it, it failed. Also, when I opened the maintenance plan, I noticed that it said that it was unscheduled. I was hoping that by running the script, that it would know that it was scheduled. So I don't know if your solution was exactly what I was looking for.
Here is what I have.

-------------------------
The script:

USE [msdb]
GO

/****** Object: Job [MaintenancePlanTest.Maintenance_Tasks] Script Date: 03/09/2013 15:54:38 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MaintenancePlanTest.Maintenance_Tasks')
EXEC msdb.dbo.sp_delete_job @job_name = N'MaintenancePlanTest.Maintenance_Tasks', @delete_unused_schedule=1
GO

USE [msdb]
GO

/****** Object: Job [MaintenancePlanTest.Maintenance_Tasks] Script Date: 03/09/2013 15:54:38 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/09/2013 15:54:38 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MaintenancePlanTest.Maintenance_Tasks',
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'NT AUTHORITY\SYSTEM', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Maintenance_Tasks] Script Date: 03/09/2013 15:54:38 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Maintenance_Tasks',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\MaintenancePlanTest" /set "\Package\Maintenance_Tasks.Disable;false"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MaintenancePlanTest.Maintenance_Tasks',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130309,
@active_end_date=99991231,
@active_start_time=50000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

--------------------------------------------

The error reported when the job runs:

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 4:02:22 PM
Error: 2013-03-09 16:02:22.61
Code: 0xC002F210
Source: {16F936A7-2E0C-47D0-AF86-5C00BA288A23} Execute SQL Task
Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER

EXECUTE msdb..sp..." failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
Error: 2013-03-09 16:02:23.00
Code: 0xC0024104
Source: Rebuild Index
Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.
End Error
Error: 2013-03-09 16:02:23.00
Code: 0xC0024104
Source: {6EC78386-CABF-446C-9F05-A46EF9D6C3C9}
Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 4:02:22 PM
Finished: 4:02:23 PM
Elapsed: 0.827 seconds

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/09/2013 :  13:09:30  Show Profile  Reply with Quote
seems like passed subplanid is wrong. Did you by anychance noted down the initial subplanid before deleting maintenance plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000