0

I'm planning a mifgration of a SQL Server 2008 instance running the DB engine and SSIS.

The SSIS is deployed to the file system.

Am I able to copy those SSIS packages to a directory on my new SQL Server 2017 machine, open them in SSDT 2019 and then deploy to SSISDB?

Or is there some other intermediate step or process I should follow?

1

Maybe.

SSIS packages as a general rule are forward compatible - if you run a 2005 package using the SQL Server 2019 binary of dtexec.exe the first step it will do is an in-memory upgrade of the package to a 2019 equivalent version. Once the package exits, the upgraded package disappears (in-memory only) and all is well and good.

My experience is that there are two cases where the auto upgrade fails: third party components and Script Components - typically source components but async transformations are equally likely candidates. 2008 and 2012 had changes in the signature of the pipeline/scripting stuff that the upgrade wizard couldn't always auto figure out. I never figured out precisely what would throw it out of kilter but something would. Generally speaking, opening the package up in the newer version of SSDT and then double clicking the script component would force it fix itself and worse case, it'd tell you what was off once you were in there.

The important thing to know moving the SSIS project from 2008 to 2012+ is that the wizard will encourage you to move to the "new" project deployment model. Currently, you're in the package deployment model. The package deployment model allows all the packages to work at their own pace - no dependencies on any other package (at least enforced in code). You're welcome to use whatever logging you wish, same with your configuration approach and package protection levels.

But, your question specifically asks about moving to SSISDB so yeah, you get to tackle those questions. With the Project deployment model, all the packages in an SSDT project need to be in harmony.

Oh and a while I've not tested since the release candidate era, they introduced Incremental Package deployment to the SSISDB with 2017.

  • Fire up ISDeploymentWizard (C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe)

  • Change the deployment model from Project Deployment to Package Deployment enter image description here

  • Select the package(s) of interest

  • Connect to your server and create the deployment folder and then create a new project to hold them enter image description here

  • Click Next, Click Deploy your packages and cross your fingers enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.