The project deployment model was first introduced in SQL Server 2012, which enabled users to deploy individual SSIS projects via Visual Studio (or SQL Server Data Tools) to an SSIS Catalog12 4. While this was a major step up in the way SSIS packages could be deployed, there was a challenge in deploying individual SSIS packages. There was no way individual packages could be deployed without deploying the entire package project.
This was an issue because all the packages from the project had to be deployed to the SSIS catalog, even the ones which were being modified and were not complete. This would cause issue during the project execution on the deployment server as incomplete packages would fail thereby causing any dependent packages to fail or not run. While there were multiple alternatives to get around this issue (discussed in detail SQL Server Integration Services 2016 Incremental Package Deployment article 3), the path to deploy a single package was often convoluted.
With the release of SQL Server 2016, incremental package deployment was introduced which enables users to deploy individual packages without deploying the entire project they are part of. This is a very nifty feature as any repairs and enhancements can be drip fed to the production code without breaking the entire solution running on the production server. This also eliminates the additional effort to implement the alternatives3.
Steps to Incrementally Deploy an SSIS Package
In Fig 1, we can see that packages that have been created as a part of TrainingSSIS project in the visual studio environment (green box) have been deployed to integration services catalog (red box) on a database instance. Imagine if there was a requirement to add a new package to an existing project or make a change to an existing package and then finally deploy it to the server.
Fig 1
In SQL Server 2012, the user would have to deploy the entire project (TrainingSSIS) to the server thereby deploying any packages that may have been modified in the interim. With SQL Server 2016, a single package can be deployed to the catalog without deploying rest of the packages in the project in the following steps
Step 1: Create a package as shown below in Fig 2. Since we are just testing how a single package can be deployed, there is no need to add any control flow components.
Fig 2
Step 2: Right click on the package you want to deploy and select Deploy Package
Fig 3
Step 3: Press Next to move to the next screen in the wizard.
Fig 4
Step 4: Enter the ServerName of the database instance where the package needs to be deployed. Then select\enter the project folder Path and press Next as shown in Fig 5.
Fig 5
Step 5: Press Deploy in the review screen to deploy the package.
Fig 6
Step 6: Once packages have been successfully deployed as shown below, press Close.
Fig 7
Once the package has been deployed, it will be available in the SSIS catalog as shown below in Fig 8.
Fig 8
A similar process would be applicable if an existing package had to be modified and re-deployed to the relevant database instance.
Conclusion
Incremental package deployment feature in SQL Server 2016 thus eases the deployment of individual packages without the need to deploy the whole project.
References
- https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog
- https://www.mssqltips.com/sqlservertip/4097/understanding-the-sql-server-integration-services-catalog-and-creating-the-ssisdb-catalog/
- https://www.mssqltips.com/sqlservertip/3676/sql-server-integration-services-2016-incremental-package-deployment/
- http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/159270/?utm_source=SSC&utm_medium=pubemail