By: Koen Verbeeck | Updated: 2015-07-15 | Comments (3) | Related: > SQL Server 2016
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.
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.
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.
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.
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.
After you have successfully deployed the packages, they are now stored in the SSIS catalog.
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
- To learn more about the SSIS project deployment model, check out this tips:
- These two articles describe the differences between the package and project deployment model in SSIS:
- Read the following articles to learn more about deploying in SSIS:
- SSIS Deployments (using older versions)
- SSIS Deployments with SQL Server 2012
- The following tip gives an overview of all the new features in SQL Server 2016 CTP2.
About the author
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
Article Last Updated: 2015-07-15
