I am writing this post 10 Jul 2019.

Yesterday, support ended for SQL Server 2008 and 2008 R2. Many are still running SQL Server 2008 and 2008 R2 in production, and many are using SSIS 2008 and 2008 R2 in production as well.

If you are reading this post and thinking or saying, “Yep. He’s writing about me,” you are not alone. If you feel bad about this, I have spoken to people who are still running DTS (Data Transformation Services) from the SQL Server 2000 days.

If you’re feeling smug because you’re running SQL Server 2016 or 2017 in production, test-drive the moccasins of folks who are hampered by mission-critical third-party enterprise solutions for which later SQL Server versions have not been certified – some of whom are operating in regulation-heavy industries. In sum, there are legitimate reasons some folks continue to run outdated (and unsupported) enterprise software.

Upgrading SSIS

When it’s time to upgrade, upgrading SSIS is fairly straightforward: Open your latest version of Visual Studio and open the SSIS solution file. Visual Studio detects the older version and fires up an upgrade wizard. The upgrade wizard creates a backup of the original SSIS project by default (unless you uncheck that checkbox) and walks you through the upgrade process.

Some things may require manual intervention (Script Tasks and Script Components leap to mind). If script objects in your SSIS 2008 or 2008 R2 packages aren’t hitting version-specific .Net assemblies, the code they contain is updated by opening the Script Task or Component editor, opening the VSTA (Visual Studio Tools for Applications) editor by clicking the Edit Script button, closing the VSTA editor, and then clicking the OK button to close the Script Task or Component editor.

Upgrading to the SSIS Catalog

If you are using SSIS 2008 or 2008 R2, you are executing SSIS packages in what is now called the “Package Deployment Model.” Package Deployment Model existed prior to SSIS 2012’s release, but we had no name for it (and no need for a name – there was only one deployment model before the SSIS Catalog appeared in 2012).

When Visual Studio upgrades your SSIS 2008 or 2008 R2 project, the project is imported as a Package Deployment Model project:

The newer, SSIS Catalog-enabled deployment model is called Project Deployment Model. If you want to upgrade your SSIS packages to use the SSIS Catalog, the SSIS project needs to be upgraded from Package Deployment Model to Project Deployment Model. Upgrading to Project Deployment Model is not uncommon and, thanks to the SSIS Team at Microsoft, it’s not hard. Right-click the SSIS project in Solution Explorer and then click “Convert to Project Deployment Model”:

The Integration Services Project Conversion Wizard starts…

… and does what it says on the label:

Granted, I skipped a bunch of steps.
Note the SSIS project icon has changed (no longer color) and “(package deployment model)” in parentheses is missing at the end of the SSIS project name in Solution Explorer. These differences are but two of many indicators that this SSIS project is now converted to Project Deployment Model.

13 Jul 2023 Update

SQL Server 2022 has been generally available for months and my cool DBA friends are liking what they’re seeing. I have not personally attempted to upgrade a SQL Server 2008 instance to SQL Server 2022, so I recommend you practice and test your migration using SQL Server 2022 Developer Edition in a non-production environment first.

As I describe in a couple posts – linked in the title post named One Way to Install SQL Server 2022 Developer Edition – SQL Server 2022 Developer Edition is free and has all the features of SQL Server 2022 Enterprise Edition.

“We Need Help”

Again, you are not alone. Our teams at Enterprise Data & Analytics regularly help clients migrate from earlier versions of SSIS to more recent versions. We also help clients lift and shift SSIS to Azure-SSIS in Azure Data Factory.

We specialize in Data Integration Lifecycle Management – or DILM (we literally wrote the book on it). We help clients gain confidence in their enterprise data integration solutions and infrastructure by practicing DevOps for SSIS. We also built and maintain DILM Suite, a collection of mostly free (and some open source!) utilities and solutions that address gaps in the enterprise DILM story.

Contact us to learn how we can revolutionize the way you build and manage enterprise data integration in your data estate. We are here to help.™