Move SSIS Packages from SQL Server 2008 R2 to SQL Server 2016

3
votes

We are moving all of our SSIS packages from SQL Server 2008 R2 to SQL Server 2016. I suspect each SSIS package will need to be upgraded but I have been told otherwise.

Will all SSIS packages need to be upgraded to to work on the new SQL 2016 server? Should they be upgraded on the SQL 2008 R2 Server and then moved to the new Server? Or should we move the packages and then do the upgrades?

Also we have a large number of SSIS packages to upgrade. Will the SSIS Upgrade Wizard do these upgrades in bulk?

Here is a rough process that I am proposing we follow:

  • Use Data Migration Assistant to check for potential issues
  • Use SSIS Upgrade Wizard to update the packages
  • Manually resolve any issues
  • Test the packages

Are there any other things I should be concerned with?

3
sql-serverssissql-server-2008-r2sql-server-2016
I would really suggest attach them to Visual studio and they will automatically upgrade and you will get the message And, while moving bulk packages you may have to rollback and check if one fails with in wizard – Ven
What do you mean by "attach them to Visual Studio." They have all been created with Microsoft Visual Studio 2008 and deployed to the 2008 R2 Server. Do you mean just open them with a newer version of Visual Studio, i.e., VS 2017 and upgrade them with Visual Studio? – navig8tr
Thats right, open them with newer version of visual studio – Ven
I also recommend opening and updating each package in SSDT/Visual Studio. You will want to verify everything. About 20% of our packages (the oldest ones) had things that didn't migrate correctly which resulted in data files generating incorrectly. You will probably want to migrate to SSISDB, too. The older methods of storing packages are considered legacy in Sql Server 2016, IIRC. You will find that SSMS will have difficulty managing jobs unless you're local to the server and using SSMS 2016 if you don't. – Bacon Bits

3 Answers

2
votes

I upgraded a set of SQL Server Integration Service packages recently by just importing them to a SQL Server Data Tools project.

The upgrade is done automatically and hands free by Visual Studio. Then you can attach them to 2016 SQL Server Integration Service .

I also recommend you check the following link from Microsoft that describes how to upgrade SSIS packages using the Package Upgrade Wizard :

Upgrade Integration Services Packages Using the SSIS Package Upgrade Wizard

1
votes

The way we are upgrading is to create new projects/solutions in SSDT 2016 (Visual Studio), import the existing 2008 packages into the 2016 projects, and letting Visual Studio perform the upgrade while converting them to the Project Deployment model. Then we deploy them to the 2016 SSIS Server.

There are multiple ways to do it, and which way is "best" is a matter of opinion and may vary from case to case.

0
votes

I agree that the best way to migrate is to create a new project in the VS 2016 solution, as the projects for VS 2008 and VS 2016 differ and you can not simply migrate them with any tool.

Here were my steps for migrating: 1) Open Command Prompt, run SSISUpgrade.exe from C:\Program Files\Microsoft SQL Server\130\DTS\Binn (Note path may vary depending on the version of SSDT you are using)

2) In the SSIS upgrade tool specify the path to VS 2008 solution and follow all the wizard steps up to finish.

3) Open the upgraded solution with VS 2016, remove the project from it(perhaps it didn't loaded correctly)

4) Create the new project of type Business Intelligence - Integration Services Project

5) Right click on the project -- add existing item, and then add dtsx package, config file for, all Powershell scripts and all other stuff than is used by this dtsx package

Keep mind, that VS 2016 will create a new folder for the project inside the solution folder, and place dtsx files, and all the stuff you've added in step 5. And all of it should be kept in this separate folder, otherwise solution won't work.