SQL Server Integration Services 2016 Incremental Package Deployment

By:   |   Comments (3)   |   Related: > SQL Server 2016


SQL Server Integration Services (SSIS) 2012 introduced a new concept of managing projects and their packages: the project deployment model. Using the project deployment model, it was not possible to deploy individual packages to the server. In the new and upcoming release of SQL Server 2016, it will be possible to deploy packages incrementally to the server, just like in the old versions of SSIS. In this tip we will take a closer look at this capability.


The issue with older versions

In SQL Server Integration Services (SSIS) 2012 the project deployment model was introduced. One of the features of this deployment model is that projects are treated as an actual object that can be deployed, in contrast with earlier versions of SSIS where a project was just a Visual Studio concept. A consequence is that you can only deploy projects in SSIS 2012 and SSIS 2014 when using the project deployment model, itís not possible to deploy individual packages.

This might result in some difficult scenarios. Suppose you have a single project with two packages: package A and package B. In a previous deployment, you have deployed working versions to the SSIS catalog (the central storage and management entity in SQL Server for the project deployment model) on the server. You are adding new features to package B, resulting in a currently unfinished not-working version of package B in Visual Studio. Suddenly a bug is discovered in package A. You fix it and you want to deploy package A as soon as possible to the server. However, since you are working with the project deployment model, you need to deploy the entire project to the server, meaning a not-working version of package B will be deployed as well, which will break the build at the server.

To solve this issue, there were three options:

  • Use source control and branch the project in two different branches, one with the older, working version of package B. Fix the bug in package A and deploy this branch to the server. The other branch contains the latest version of package B. When A is fixed in the first branch and package B is finished in the other branch, the two branches need to be merged into one single final branch. This solution requires more discipline from the ETL developers and of course the right toolset to make this possible. The SSIS development team suggested this as the ideal solution to the problem. In reality however, it seemed that some percentage of business intelligence developers donít have a strong background in application life cycle development, making this solution seem unnecessary complex when in older versions of SSIS you could simply deploy a single package. This resulted in frustration because a simple solution was replaced by a more complex one (as noticed on some MSDN forum threads).
  • Use source control again, but now just roll back package B to its older working version. Fix package A and deploy the project. Restore package B again to its latest version. This is a more pragmatic approach to solving the issue with source control, but requires you to keep track at any time which packages have been modified since the latest release, which might become difficult for larger projects.
  • Donít use the project deployment model, but the package deployment model. This deployment model is functionally exactly the same as how SSIS worked in versions 2005 to 2008R2. This model is kept around in all new versions of SSIS as backwards compatibility. Itís useful for teams that have large projects in older versions and who donít want to migrate everything to the new project deployment model. However, this options means you canít use all of the new features of SSIS 2012, such as the SSIS Catalog, parameters and project connection managers.

SQL Server Integration Services Incremental Deployment

In SSIS 2016, an option is added so you can deploy individual packages again to the SSIS catalog, instead of the whole project. At the time of writing, there was no SQL Server Data Tools for Business Intelligence (SSDT-BI) released that supported this new feature. This means there are no screenshots of this new feature from Visual Studio. However, we can still test the functionality using the Integration Services Deployment Wizard. Functionally, the wizard is exactly the same when deploying a project from within Visual Studio.

When you have installed SQL Server 2016 CTP2 preview or a later version, you can find the wizard in the start menu.

Start Menu

After the Welcome screen, you need to select the deployment model. The default is the project deployment model and the screen is exactly the same as in the previous versions of SSIS: you need to select an .ispac file which you want to deploy. To test the functionality in SQL 2016 CTP2, you can deploy any project you created with SSIS 2012 or SSIS 2014.

Project Deployment at the Wizard

Using the dropdown, you can change the deployment model to package deployment model. Now you need to browse to a local folder where SSIS packages are stored.

Package Deployment at the Wizard

In the above screenshot I browsed to a project where two packages are located. Since the first package returns an error, I can now safely disable it so I can deploy only the second package to the server. Using this deployment model, it doesnít matter if you created the SSIS packages using the project or package deployment model in Visual Studio.

At the next screen, you need to select a destination server and a destination path at the SSIS Catalog. Remark that there is no option to deploy to the MSDB database.

Setting the destination

If you are deploying for the first time, you need to create a new project in the SSIS catalog. If you have already deployed before, you can select any project that is located in the SSIS catalog.

Creating a new project using the deployment wizard

After you have successfully deployed the packages, they are now stored in the SSIS catalog.

Eureka! Only one package was deployed!

Using the package deployment option, it is now also possible to deploy packages from one project to another project.


Using the new incremental deployment option in SSIS 2016, it is now easier to deploy changed packages to the SSIS catalog. You can choose any packages you want and deploy them to any location on the SSIS catalog, allowing much more flexibility than in previous versions of SSIS.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, May 11, 2017 - 7:07:07 AM - Koen Verbeeck Back To Top (55673)

Hi Ovi,

when you are working with the project deployment model, you can only deploy to the catalog. You can convert your project to the legacy package deployment model. In that case, you can deploy to MSDB. Keep in mind this means you don't have parameters, environments, project connection managers etc.



Wednesday, May 10, 2017 - 7:06:07 PM - Ovi Back To Top (55660)

 Is it possible to deploy packages to msdb in SQL Server 2016 or is SSISDB the only option?


Wednesday, August 10, 2016 - 12:47:02 PM - Anne Back To Top (43095)

We are still using SQL 2014, and not have a plan to upgrade in near future.

If microsoft can release a service pack for  SQL 2014 to add the incremental deployment, that will be great.

get free sql tips
agree to terms