Changing the reference instance for SQL Server Integration Service (SSIS)

Many people do not know how to manage multiple database instances and Integration Services, while facing the end of life for Integration Services reference instance. Let’s take a small example with Integration Service installed using instance “TOTO”, which comes end of line. We will have a look to SSIS information transfer to a second instance “TITI”. This article proposes a small working method to transfer these information correctly. We start with the folders transfer and then get an eye on SSIS packages transfer.

Environment for the Lab

SQL 2008 R2 on Windows 2008 installed with two instances:

• TOTO: SQL Server Engine / SQL Server Agent / Integration Services (SSIS)
• TITI: SQL Server Engine / SQL Server Agent

Note that Integration Services is not attached to an instance like the others services in the “SQL Server Configuration Manager” tool.
In Integration Service, we create two folders: TITIDB and TOTODB in “Stored Packages” – “MSDB” folder
Have a look down there:

Looking for the SSIS folder information

Folders are stored in the MSDB system database of the Instance with which Integration Services is installed. The table is “sysssispackagefolders” in the system tables.

Just do for each instance a SELECT * FROM [msdb]. [Dbo]. [Sysssispackagefolders]

Instance TOTO

Instance TITI

Note that the instance TOTO has the two folders information and TITI has no information.

Methodology to transfer folders information

1. As start point, stop Integration Services with “SQL Server Configuration Manager” tool
2. Then, copy the 2 lines from the Sysssispackagefolders table from the instance TOTO and paste them with a right-click in the Sysssispackagefolders table of the instance TITI.
3. Now, change the default instance of the XML configuration file for SSIS. To do so edit the file  “MsDtsSrvr.ini.xml” in the folder “C:Program FilesMicrosoft SQL Server100DTSBinn”. Simply replace the tag .TOTO by .TITI and save the file.
4. Stop Instance TOTO with “SQL Server Configuration Manager” tool
5. Start Integration Services with “SQL Server Configuration Manager” tool
6. Verify that it’s running. A good test is to create a folder or subfolder in Integration Services

If it doesn’t work, you may have an error message…

With the method shown, you can easily change the default instance and their folders in Integration services (SSIS). Of course re-creation the whole folder structure still work but the step 3 will have to be made in any case.

Facing and Error?

If your are testing your Integration Services environment but the transfer didn-t properly complete, then you will face the following error:

This message is not really explicit. The best is to give one more try by following step by step the method shown above.

Package transfer

The last step is to transfer all SSIS packages, using import, to the new reference instance. Start by running the follwing SQL statement SELECT *  FROM [msdb].[dbo].[sysssispackages] on the instance TOTO in order to check all SSIS Packages registered. Then import all of them one by one through each folder and sub-folder.

So I hope that this article will help for future Integration Services migration and if you need any information feel free to contact us.