When I migrate SSIS packages...

BTW: This is my first blog in this site.

Last week I had a task to transfer some SSIS packages, which were developed on Visual Studio 2010 and deployed on SQL Server 2012 to a new server which had Visual Studio 2022 and SQL Server 2022.

Using Sorcery to import...

Since there is not much has changed in SSIS since 2012, you would thought it is straight forward, but I faced few challenges.

First I tried copied SSIS solution to new server and directly open them on Visual Studio 2022. I thought Visual Studio 2022 will prompt me to convert them to new version and carry on. But for my surprise it didn't and opened the solution without any sound. But when I open the package, it threw me errors in almost all components.

After some investigations, I found Visual Studio 2022 SSIS extension has a project template which I can use to import SSIS project directly from the SQL server, SSIS catalogue.

It is called "Integration Service Import Project Wizard". For my surprise, it worked very nicely and pretty each to done with intuitive wizard steps/UI. It ask you to connect to where existing SSIS packages are and allows you to browse through SSIS catalogue.

After I imported all packages as new SSIS 2022 projects/solutions, I have opened them on Visual Studio 2022 without any issues.

Protection Level Confusion...

My old SSIS packages had sensitive information encrypted with password (because multiple users handled them). When I opened them on Visual Studio 2022, they didn't asked me to enter password. I was puzzled first. Then I realised import wizard has change the "Protection Level" of the package and solution to "EncryptSensitiveWithUserKey".

Hmm.. why?

For those who new to Protection Level there are 5 protection levels in SSIS:

I tried to change it to "EncryptSensitiveWithPassword" and save. Package is not saving!!!

Plus, there are no error messages!!! Weird. Tried several times, restarting VS, but no luck.

From following article and some similar articles, I have realised that, Visual Studio 2022 SSIS extension has some sisues with protection level.

https://stackoverflow.com/questions/65852869/ssis-cannot-change-project-protectionlevel-to-encrypt-sensitive-data-with-passwo

So I kept the protection level as it is and decided to same the sensitive information under user key.

Target Server Version Does Matter...

This issue came up when I try to run the package on SQL Server it self after deploying the new package. Although I have moved packages to Visual Studio 2022, our SSIS server was on 2019. When Import Wizard import packages, it set the target version to 2022, so I learned we need to manually set it to 2019 before deploying to the server. Unless some of the components (specially scripting components, doesn't work).

Hope this information help some one. May be it will help me in future too.

That's my tale on SSIS package migration.