Import Main Plan Pkg and schedule it with scripts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Import Main Plan Pkg and schedule it with scripts

Author

Topic

mciancia
Starting Member

2 Posts

Posted - 2013-03-09 : 00:06:54

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

52326 Posts

Posted - 2013-03-09 : 01:08:01

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/

mciancia
Starting Member

2 Posts

Posted - 2013-03-09 : 11:15:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-09 : 13:09:30

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/

Darren996
Starting Member

5 Posts

Posted - 2014-09-15 : 15:15:42

I know this thread is old but I have this exact same problem. Did this ever get fixed?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources