Running a Maintenance Plan Using dtexec.exe Command Prompt Utility Does Not Perform Any Action

  • Article

SQL Server Maintenance Plans bring an easy way to DBAs to quickly setup routine maintenance activities in the database. Starting SQL Server 2005 maintenance plans are created as SQL Server Integration Services packages and could be executed in the same way as any other SSIS package:

  • Using Business Intelligence Development Studio (BIDS)
  • Using the SQL Server Import and Export Wizard
  • Using SQL Server Agent jobs
  • Using the graphical tool DTExecUI
  • Using the command prompt tool dtExec.exe

In the case I want to discuss now I was trying to run a maintenance plan package using the dtExec.exe utility. My goal was to execute a backup task using this maintenance plan and running this from command prompt did not show any error but did not perform any backup either. This was the syntax I was using and the results:

C:\>dtexec /SQL "Maintenance Plans\Backup MP" /Server SQL2005\YUKON
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 15:21:20
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 15:21:20
Finished: 15:21:51
Elapsed: 31.046 seconds

My first thought was, "do I need to have SSIS service running in order to execute this maintenance plan package"? but Microsoft Knowledge Base article KB942176 clearly states that SSIS service is not required for this:

"If you only want to design and to execute SSIS packages, you do not have to start the SSIS service. When the SSIS service is stopped, you can run SSIS packages by using the following utilities:

  - The SQL Server Import and Export Wizard
- The SSIS designer
- The Execute Package utility (DTExecUI.exe)
- The DTExec.exe command prompt utility"

When you create a maintenance plan using SQL Server Management Studio you will see that the corresponding task or tasks associated with that maintenance plan are created as a SQL Server Agent job:

image

 

You can easily find what specific dtexec.exe parameters are provided on the maintenance plan just by looking into the SQL Server Agent job properties, on the "Command line" option for the subplan definition:

image

 

I checked that by running this sentence as the dtexec.exe parameter successfully run the backup job, in my case this is the command prompt instruction I used:

DTEXEC.EXE /SQL "Maintenance Plans\Backup MP" /Server SQL2005\YUKON /SET "\Package\Subplan_1.Disable";false

The "tricky" part here refers to the /SET switch. As you can find in the maintenance plan GUI each maintenance plan has one or more subplans associated and this/theses are disabled by default so you need to enable them by issuing the "false" option for the "Disable" property of this subplan. Remember that each subplan defined in the maintenance plan creates a job under SQL Server Agent.

We can perform the same action right-into the SSIS package associated to the maintenance plan using SQL Server Business Intelligence Development Studio (BIDS):

  - Open BIDS
  - Create a new empty SSIS project
  - Add the existing maintenance plan package to the Project files list by using Solution Explorer; the maintenance plan will be added as a new SSIS Package
  - Modify the "Disable" property of the subplan so it is equal to "false"
  - Save the SSIS package as a maintenance plan in SQL Server
  - Run the maintenance plan without the need of specifying the /SET "\Package\Subplan_1.Disable";false switch