Are you considering upgrading SQL Server Integration Services to Version 2012? If yes, then this is the blog that will help you understand the details of the process.
Taking advantage of the improvements made in new releases of SQL Server related software can sometimes be daunting when faced with the complexities of doing an actual upgrade.
Are you considering upgrading SQL Server Integration Services to Version 2012?
If yes, then this is the blog that will help you understand the details of the process. We have attempted to put the essential pieces together to explain the same process we went through with a recent upgrade done for one of our clients. Let’s begin with the following:
1. You must have an SQL Server 2005/2008/2008 R2 Integration Services.
2. The server should have a minimum of 6 GB of free disk space. It is recommended to use the windows NTFS based file Format.
3. It is recommended to use the Upgrade Advisor before proceeding with Upgrade Process. Please note that for installing Upgrade Advisor, you must have “SQL Server 2012 T-SQL ScriptDom”.
It can be downloaded from the link below from the Microsoft site:
The rest of the pre-requisites are same as for SQL Server 2012 database engine.
B. POINTS TO REMEMBER (Pre-Upgrade)
1. You can install integration services side by side to the earlier version of it on the same host but it is important to note that you should be calling up the correct dtexec utility when it is required, post upgrade.
2. You cannot use the Upgrade Method or Integration Services 2012 to do the following:
a. Migrating DTS Packages
b. To use DTS Execute utility
c. Data Source Views
d. ActiveX Script Tasks
e. Reconfigure an Existing Installation of Services
f. Move from 32-bit to 64-bit or vice-versa
g. Move from one localized version of SQL Server to the other localized version
C. POINTS TO REMEMBER (During upgrade)
1. In case of an In-Place Upgrade Method; the SSIS service under context of upgrade is migrated to 2012. It upgrades the SSIS files, service and tools (SSMS and Data tools).
2. If it is Side-by-Side Upgrade method; process does not remove the earlier version which is already installed; rather it installs the new version and leaves the earlier version intact. This is why I mentioned in Point B.1; you should be cautious when calling your dtexec Utility from your existing jobs post upgrade.
3. Upgrade Process does the below Metadata Upgrade process
a. Moves packages from msdb.dbo.sysdtspackages90 system table (2005) to the msdb.dbo.sysssispackages system table WITHOUT any Change.
b. Moves folder metadata from the msdb.sysdtsfolders90 system table (2005) to the msdb.sysssisfolders system table.
c. Moves log data from the msdb.sysdtslog90 system table (2005) to the msdb.sysssislog system table.
|A very Important Point to note here is that the upgrade process does not upgrade the packages at this step but moves the packages and its Metadata to the new designated folders.|
d. Also; earlier tables/folders and package locations are updated to a new version and also the configuration file which now points to a new/upgraded Instance.
D. POINTS TO REMEMBER (Post-Upgrade)
Discussed below is one of the most important facts which may actually stop your upgrade request to completion
Earlier to Version 2012; upgrade of an integration service automatically created the required user access groups and thus all the user levels were able to access an “upgraded version of the integration services” by default.
Since 2012; Microsoft has stopped this facility and made integration services more secure using DCOM (Distributed Component Object Model) object concept.
DCOM - It is a Microsoft concept based on COM. DCOM allows networking in which “client based objects” can request services from “server based objects” from other computers in the network.
Here in our case; DCOM refers to a tool named dcomcnfg.exe which actually allows to modify certain settings in the registry.
Error Screen you would see
As soon as you are done with upgrade and trying to access SSIS services using SSMS (SQL Server Management Studio), you would get the below error. Yes, it is quite surprising that after having admin rights on Host, still this error is encountered.
Follow the steps below to resolve your problem:
1. Run dcomcnfg.exe from command prompt or PowerShell with the administrative rights.
2. In the Component Services Folder; expand the Component Services > Computers > My Computer > DCOM Config Node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for access permissions.
7. Restart the SQL server management studio.
8. Restart the integration services service.
Now you can go ahead and complete your Integration Service Upgrade process which may be pending like upgrading data collector packages or user SSIS packages.
The following given below are checks to confirm if packages you are referring are actually upgraded or not:
a. Check the column “packageformat” to identify the version of packages in the msdb.dbo.sysssispackages system table.
a. If packageformat = 2; Package Format is SQL Server 2005 Integration Services (SSIS).
b. If packageformat= 3; Package Format is SQL Server 2008 Integration Services (SSIS).
Until you migrate packages into a new package format, the value in the packageformat column does not change. This helps ensure if the package upgrade is a success or not.
Apart from above point; being a pro-active DBA; we can check and change the below registry setting as per recommendation from the Microsoft-
When we use “Data Collector” feature of SQL Server 2012; upgraded version is by default configured to log each and every event of the package execution. These may fill up your all Application Event Log Folder in the Event Viewer with EventID=12288 (package started) and EventID=12289 (package finished successfully).
To prevent this, we can do the below steps:
1. Open regedit.exe from the command prompt with the administrative rights.
2. Locate HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS node and change the DWORD value of LogPackageExecutionToEventLog from 1 to 0.
The above overview can help guide a SQL Server Integration Service Upgrade to version 2012, especially when in-place upgrade is requested. Because upgrades often face unexpected issues and can be technically challenging, Tri-Core offers its expertise in this area and would be happy to assist you in any Microsoft SQL Server related upgrade.
For any queries reach out to me at vssharma@Tricoresolutions.com