Today came across an issue on a SQL Server 2005 Named Instance. The requirement was to edit one of tasks in a Maintenance Plan. After opening the Maintenance Plan, when double clicked on the task to be edited, SSMS threw the below error message.Value of ‘7/9/2010 12:00:00 AM; is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.
The problem was that the “Backup Set will Expire” was set to today i.e. 7/9/2010 in the Maintenance Plan (not sure how !). I could not see it since the step was not opening up, so this is an assumption. To workaround this problem, the following tasks were performed. First opened the Maintenance Plan outside SSMS for editing.
- Opened Business Intelligence Development Studio on the Server
- Created a new Project
- Right click on the SSIS package and selected “Add Existing Package”
- In the next dialogue, selected SQL Server and connected to the SQL Instance
- Clicked on the “Backup Database” task and navigated to the “ExpireDate” value
- Deleted the value ‘7/9/2010’ and left the field blank
- Saved the package to disk locally
Now the package was edited and ready to be imported back into the Maintenance Plan. So, fired up SSMS and connected to the Integration Service on the Server. Navigated to Stored Packages and clicked on MSDB. But after a long wait, got an error message which indicated that SSMS was unable to connect to the instance. At that time I remembered that the instance was a Named Instance and SSIS was trying to connect to the MSDB database of the Default Instance (which never existed). The next step was to modify SSIS to connect to the Named Instance’s MSDB. Here is what was done.
- Navigated to C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder on the server
- Opened MsDtsSrvr.ini.xml in Notepad
- The value of MSDB database was
- The <ServerName> was pointing to “.” i.e. the Default Instance.
- Modified it to
- Saved the file
- Restarted the Integration Services service for the changes to take effect
Now the SSIS is ready to import the modified package. The file was imported back to Maintenance Plans as below.
- Connected to SSIS using SSMS
- Navigated to Stored Packages –> MSDB
- Right clicked on Maintenance Plans and selected Import Package
- Selected File System in the next window and pointed to the package stored locally on disk
- Selected Overwrite when asked for confirmation to overwrite the existing package in Maintenance Plan
Now headed back to the Maintenance plan and double clicked on the “Backup Database” task and it was ready to be edited !