1

We have an SSIS project in Visual Studio (2019 Community) that we deploy to our SQL server (2016) using project deployment. We have a couple of hundred packages and we most often deploy single packages at a time.

We had been doing most of our development in a single Azure Git master branch since these changes were separate from most of our other development. A few changes were done in a different development branch and then that single package was deployed from there. It should not have caused a problem because that single package was new and not being used in the master at all. However, when that single package was deployed it removed a couple of new packages from our live server that had been deployed from the master branch but had not been in the development branch when it was initially created.

I thought that a single package deployment only deployed that single package you selected or the few packages you selected if you chose a few of them. It seems like in addition to deploying that package it deleted packages that were on the live server but not in the dev branch that we deployed from.

We deploy from Visual Studio by right-clicking our package or packages and then choosing Deploy Package from the context menu. That process creates an .ispac file when we are deploying to our development SQL Server. That .ispac file is copied to the live server and then run directly on the live SQL server.

This should not be deploying all packages, correct? Just those highlighted? Does it remove missing packages if I only deploy a single package at a time but the packages in my project in VS and the project in the SSIS catalog do not match?

LarryG
  • 627
  • 1
  • 6
  • 14

1 Answers1

0

It depends!

For 2012 and 2014, the only option for deployment to the SSISDB was "Project" deployment. The entirety of the .ispac file is applied to the SSISDB - whether package1 had changed or not.

With 2016+, you have the ability to do Project or Incremental Package deployment. In the "Review" tab of the deployment wizard, in the Summary section you can see the command line indicates /ModelType:Package That is the indication it is deploying package(s) versus the whole project. See figure 5 of https://www.sqlservercentral.com/articles/incremental-package-deployment-%E2%80%93-a-ssis-2016-feature

If you're deploying from the .ispac, then there should be a selector in the Select Source tab (not shown in referenced article) that allows you to do full or incremental deployment but the ModelType in the Review tab will identify which approach was selected. I suspect someone did a full/project deploy.

But, the nice thing about the SSISDB is that you can use SSMS to roll the project back to the previous state so no harm, no foul, no lost packages.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I re-created the same issue. I did not do a full deployment but it definitely is stripping away those other packages that are missing. That's why this makes no sense. This is something we do frequently and the main difference is switching the branch to a branch that is missing a few of the newer packages that have been deployed already. This will all solve itself when we merge branches after our next main production posting. However, I'm trying to figure out why it would delete packages that are not selected for updating. – LarryG Oct 11 '21 at 17:27