Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Integration Services 2016 Incremental Package Deployment


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

Attend these FREE MSSQLTips webcasts >> click to register


Problem

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.

Solution

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.

Conclusion

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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.

 

Koen


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

 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

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.


Learn more about SQL Server tools