Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!

With the release of SQL Server 2012 comes a whole host of improvements to Integration Services that makes development and administration of your SSIS packages much easier. And it also looks new and shiny, so you can’t beat that.

After you’ve upgraded to SQL Server 2012, you’ll have to start converting your SSIS packages to 2012, which isn’t all that difficult. There are, however, some fundamental changes to the way packages are built, configured, and managed that are important to know. In this article, we’re going to walk through upgrading an SSIS 2008 project to 2012.

First, lets open SQL Server Data Tools.

0 open SQL Server Data Tools

Then we’ll need to open our existing SQL Server 2008 Integration Services project.

1 open project

Once the navigate to the project and open it, the Visual Studio Conversion Wizard will open. This will walk you through converting the 2008 IS Project to 2012.

2 VS conversion wizard

After clicking Next, you’ll be asked if you want to create a Backup. I opted to create the Backup in case something went horribly wrong. Hopefully you’re packages are in source control so you have something to fall back to, but I didn’t convert the original packages anyways. I created a copy of the project and then converted the copy in order to preserve the original project.

3 backup packages

Click Next a couple times and surprisingly, or not, another wizard will open. The SSIS Package Upgrade Wizard is where we’ll select which packages we want to upgrade to 2012.

4 ssis package upgrade wizard

Place a check mark next to the packages you wish to upgrade. In my case, I only have one package in this project, but if I have multiple packages I would see those packages displayed in the window below.

Also, this is where you will specify a password if the packages are encrypted with one.

5 select packages give pw

On the next screen of the wizard, there are a couple of important things to point out here.

6 select ssis package mngmt options

The first option, “Update connection strings to use new provider names”, allows the upgrade wizard to update any connection strings to use the SQL Server 2012 Native Client Library. This will upgrade any connections stored in your package. What this won’t upgrade are package configurations or any expressions that may be altering a connection manager. Those will need to updated manually.

Also, the last option, “Ignore configurations”, basically allows you to tell the wizard to not warn you about potential problems with package configurations on connection managers. I left this option unchecked because I want to know which packages have configurations on the connections that could potentially cause problems.

In the Upgrade Report, you’ll notice the first informational message indicates that the provider was upgraded to SQLNCLI11. Also, the upgrade wizard detected a package configuration attempting to configure the connection. We either need to upgrade the package configuration to use the new provider name or remove the configuration.

7 view upgrade report

If I open the package in Data Tools, you’ll see the following error due to the lack of support for the old provider.

8 package config provider error

So at this point we have two options. The first option is to update the package configuration and continue using our package configuration with our 2012 packages. Or we can take advantage of a brand new feature in SSIS 2012: Parameters! With your package open in Data Tools, you’ll notice the Parameters tab. This is where you’ll manage your parameters that are scoped to the package. Parameters that are scoped to the project will appear in a seperate .params file that can be seen in the Solution Explorer. Parameters are designed to replace package configurations and they function like you’d imagine a parameter would. Parameters can be assigned values and the parameters can be shared between packages in the project.

9 create parameter for package config

Before we can start using parameters in our packages, we must first convert our entire SSIS project to use the Project Deployment Model. The Project Deployment Model is also new to SSIS. Basically, with the Project Deployment Model, an SSIS project is the unit of deployment for the project, whereas with the legacy deployment model, Package Deployment Model, a package was the unit of deployment. The Project Deployment Model is how we will be able to share parameters between packages. Read this article to learn more about the Project Deployment Model. If your SSIS project is currently set to the legacy Package Deployment Model, the project icon should look like this:

16 package deployment model icon

Once you’ve converted to the Project Deployment Model, the icon should look like this:

17 project deployment model icon

To upgrade your convert to the Project Deployment Model, right-click the project in the Solution Explorer and click Convert to Project Deployment Model.

10 convert project to project deployment model

This will open, you guess it, another wizard. You’ll first select the packages to include in the project. You’ll also need to specify a few project properties, like the name of the project and the encryption level. If you are upgrading any packages with Execute Package Tasks in them, you should upgrade those to use a project based reference.

The important part of the Project Conversion Wizard is converting the package configurations to Project Parameters. On the Select Configurations screen, check the package configurations that you wish to convert to parameters.

11 convert package configs to parameters

Next, you’ll need to create the parameter that will replace the configuration. Any configurations properties in the configuration file(s) will be shown here. Specify the Parameter Name and the Scope.

The Scope can either be at the Project or Package level. If you create the Parameter at the Project level, the parameter will be viewable in the Project.params file, which you can find in the Solution Explorer. If the scope is the Package level, the parameter will be found in the Parameters tab of the package.

12 create parameter

On the Configure Parameters screen, you can alter the details of the parameter. If you click the ellipses button next to the parameter, you can view the parameter details. At the bottom of this window is a check box that says “Required”. A parameter that is Required does not have a default value and must be specified at run. If the parameter is not Required, the parameter does not need to be set for the package to execute.

13 set parameter details

Because I select the Project as the scope of my parameter, I now have a .params file that houses all my project level parameters.

14 project params

If I open this file, I can see the new parameter that was created based on my package configuration.

15 parameter

And if I look at the expression on my connection, I can see the reference to the parameter.

18 parameter referenced by expression

One other really cool thing about 2012 is that objects that have an expression on them now have a little icon indicating the expression on a property of the object. Its about time.

19 expression flag

I hope you found this helpful and that you now have a basic understanding of converting your legacy SSIS packages to 2012. Check out this white paper that has some great information on upgrading to 2012. And if you have any questions or comments, please post them!

12 thoughts on “Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!”

  1. Thanks a lot Dustin, how can we do this stuff on command prompt. My requirement is to check first the version of sql server if it is 2012, then only I want to update the packages.

  2. Hi Dustin,

    Please help me to migrate SSIS package from old window server(Installed SQL server 2005) to new window server (installed: SQL server 2012)……These are two different servers………

  3. Very useful post. My questions, please: following the similar steps, (1) in my Miscellaneous I still see the original .dtsConfig file; (2) the Project.params file is at the project level, like yours; however, it does not have any of my 10 parameters (thought, I see them on the Parameters tab for my .dtsx package design, and on Package Explorer tab, here: under both Parameters and Variables nodes).

    On the positive note: I was able to convert my old connection managers in the Connection Managers tab at the bottom of Control Flow tab – by right click -> (I believe) Convert to Project…

    You might have another posts with samples on how to (a) add to the projects values for different environments; (b)prepare scripts to deploy the SSIS 2012 project to different environments

    1. If your project had files in the Misc folder than I’d still expect those files to be there. As far as the parameters go, make sure they’re configured for the project level during the upgrade process.

      1. Thanks, Dustin.

        They were configured correctly based on the SSIS instructions and your steps. I will try to use scripts to create them in SQL Server under the Integration Services Catalog node; thus, I will have to use the set of scripts for each environments:
        DECLARE @var sql_variant = N’\\server\folder1\folder2\folder3′
        EXEC [SSISDB].[catalog].[create_environment_variable]
        @variable_name=’FileShare’,
        @sensitive=,
        @description=”,
        @environment_name=’Test’,
        @folder_name=’Some SSISDB folder’,
        @value=@var,
        @data_type=’String’
        GO

        I am wondering whether or not the new connection managers are available for the package in SSIS, or if I should script them the similar way the parameters are scripted:
        DECLARE @var sql_variant = N’Data Source=serverName;Initial Catalog=dbName;Provider=SQLNCLI11.1;Integrated Security=;Auto Translate=;Application Name=;’
        EXEC [SSISDB].[catalog].[create_environment_variable]
        @variable_name=’Connector1′,
        @sensitive=,
        @description=’some’,
        @environment_name=’Test’,
        @folder_name=’Some SSISDB folder’,
        @value=@var,
        @data_type=’String’
        GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s