Upgrading SSIS Packages In SQL Server

In our previous article we’ve seen the types of Deployment models available in SSIS. We had also deployed packages under both the models.

As I promised, in this article we’ll upgrade older version package to a newer version. We’ll see what are the steps required when you import and upgrade SSIS 2008 packages/project to SSIS 2012 i.e. lower to higher.

So, let’s begin with fresh examples,

I’ve already created a package to perform SQL Server System databases in SSIS 2008. Below is the package diagram for the same.

package diagram

Above package will notify that backup is going to start, next it’ll create a backup directory ‘C:\Backup\’ under drive C:\

Next, it’ll perform backup of your system databases and notify success or failure.

Below is our SSIS 2008 package, which we’ll upgrade in some moment.

SSIS 2008 package

Now when you’ll open this solution in SSIS 2012 you’ll get ‘Visual Studio Conversion Wizard’ which converts your project to higher version. Below is the screenshot for the same:

Visual Studio Conversion Wizard

Click next and it’ll ask you to keep backup of your older project as shown below.

Click next

On the next window, you’ll get your project to convert.

project to convert

Click finish and your conversion is complete.

conversion gets complete

Immediately after this window you’ll get another window to upgrade your package, but I cancelled that window as we want to perform it later.

After successful conversion when you check project directory, you’ll find additional folders as shown below.

check project directory

Backup folder is added to it where older project is saved as a backup, also project solution gets changed from older to newer. You’ll also find UpdatedLog file which contains details of the conversion as shown below.

UpdatedLog file

After doing all this conversion part, our package added into SSIS 2012 and as expected it’ll be exported under ‘package deployment model’. Below is the screenshot for the same.

package deployment model

To upgrade your project, right click on your project and select “Convert to Project Deployment Model” as shown below.

Convert to Project Deployment Model

After this step you’ll get “Integration Service Project Conversion Wizard” as shown below.

Integration Service Project Conversion Wizard

On this window, you’ll find 7 steps to complete the process.

Next step is to select the packages you want to include in this upgrade process.

select the packages

You can also protect this by putting password.

Click Next to specify the project properties.

project properties

At this window you’ll find Protection Level with some option listed below. Please find the following with a description for the same.

Protection Level Description
DontSaveSensitive When you specify DontSaveSensitive as the ProtectionLevel, any sensitive information is simply not written out to the package XML file when you save the package.
EncryptSensitiveWithUserKey EncryptSensitiveWithUserKey encrypts sensitive information based on the credentials of the user who created the package.
EncryptSesnitiveWithPassword Sensitive data will be saved in the package and encrypted with a supplied password. Every time the package is opened in the designer, you will need to supply the password in order to retrieve the sensitive information.
EncryptAllWithPassword This works the same as EncryptSensitiveWithPassword except that the whole package will be encrypted with the supplied password. When opening the package in the designer, you will need to specify the password or you won’t be able to view any part of the package.
EncryptAllWithUserKey This works the same as EncryptSensitiveWithUserKey except that the whole package will be encrypted. Only the user that created the package will be allowed to open the package.

Table Source.

We’ll go with option 2 ‘EncryptSensitiveWithUserKey’. Click Next.

EncryptSensitiveWithUserKey

We don’t have any execute package task, so click Next.

Execute package task

As we didn’t add any configurations to our packages, they’re not showing here. If you’ve added any configurations in your packages, select those and click Next.

configurations in your packages

If any parameter is specified in your project, select them and click Next to configure your parameters.

configure

Click Next and you’ll get a summary of your package upgrade as shown below.

 package upgrade

Click on Convert button to begin the conversion.

Click on Convert button

Our package successfully gets converted. Click on Close button.

Now if you see in the Solution Explorer, our packages are now using the new features like Shared Connection Manager, Project parameters and other features.

Solution explorer

Our project successfully converted to Project Deployment Model. Also, package deployment model gets disappeared from our project name.

Now this project works the same way other projects created in SSIS 2012.

Conclusion

In this article we’ve seen how to convert and upgrade our project from a lower version to higher version. In our next article we’ll deploy this package and perform the backup operation. Till then Keep learning and sharing.