Import Maintenance Plan - dtsx Packages (Changing Named Instance) - SQL Server 2008

Exporting and Importing Maintenance Plan dtsx Package/s from MSDB Folder - SQL Server 2008

Troubleshooting imported dts packages. If you find that your dts package you have imported into SSMS does not work check the following:

• Compatible versions of SQL Server tools.
• The xml configuration file has been edited for the correct server instance.(see below)
• The local server connection will need to be edited within BIDS as this is not changed on import.

n.b. Due to the idiosyncracies of SQL Server versions, always use packages created and intended for 2008 versions. i.e. Created in BIDS 2008 for SQL Server 2008 databases and imported via SSMS 2008. Otherwise errors may occur such as incompatible stored procedures.

Preparing the package:

For the purpose of this guide I will assume that the Maintenance Plan package/s have already been created.

1.      Open the relevant MS BIDS 2008 project and package/s.

2.      Select a Maintenance Plan package.

3.      In the ‘Connection Managers’ section double click on the ‘Local Server Connection’ to open the ‘Connection Manager’ box.

4.      Change the ‘Server Name’ to the specific server instance you want and then click the ‘Test Connection’ button. Click OK

5.      Save the package. (Save a copy of the package/s into an accessible location where we can import them from later.

6.      Repeat this process for all of your packages.

Changing the instance name in the xml configuration file:
We now need to change the DTS .xml file for configuration to the correct named instance.

1. Stop the SSIS service.
2. Open in a text editor such as notepad the following the xml configuration file: <install location>\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml
3. To configure a named instance instead of the default instance, change the existing ServerName to ServerName\InstanceName under the Folder node of type SQLServerFolder
4. To configure a named instance in addition to the default instance, add a new Folder node of type SQLServerFolder under the TopLevelFolders node
5. Save and close the xml configuration file.
6. Restart the SSIS service

Before change:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>

After change to a named instance:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>MyServer\MyInstance</ServerName>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>

After adding a named instance to the default:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  </Folder>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>MyServer\MyInstance</ServerName>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>
  </Folder>

Importing the package into SSMS 2008:

·         Whilst on the Server, open SSMS for the coinciding server version i.e. 2008. Within SSMS go to ‘Connect’ and connect to ‘Integration Services’ for the relevant instance. A new node will appear for this service in the Object Explorer. 

·         Within the Stored Packages > MSDB > Maintenance Plans folder of Integration Services, right click and select ‘import packages…’ 

·         Select ‘File System’ in the ‘Package Location’ and within the ‘Package path:’ selection, choose the path on the server where you saved the packages earlier and click ok. 

·         The packages should now appear in the maintenance plans folder. (If not, refresh the maintenance plan folder – [right click > ‘Refresh’])

[If you still cannot see your package then double-check the xml configuration file you edited earlier.]

Finally:
Open up each plan/s and carry out checks as some elements do not carry across:

1. Check the correct drive letter has been used for where the backups and logs are saved to.
2. The schedule will not have been carried across – this will need to be completed manually.
3. Check the owner of each SQL Agent job.
4. Check that the correct user databases are selected.

 Useful links:
http://www.sqlservercentral.com/blogs/robert_davis/archive/2008/12/11/How-Do-I-Configure-SSIS-to-Work-With-a-Named-Instance.aspx