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.



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.



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:



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



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



Click finish and your conversion is 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.



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.



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.



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



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



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.



You can also protect this by putting password.

Click Next to specify the 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.



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



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.



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



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



Click on Convert button to begin the conversion.



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.



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.