One of the most anticipated features of SQL Server 2016 Integration Services (SSIS 2016) is support for deploying a single SSIS package from an SSIS project.
“Why Is This A Big Deal, Andy?”
I’m glad you asked. Consider the following scenario:
-
You deploy version 1 of an SSIS Project containing 20 SSIS Packages to Production.
-
You begin working on version 1.1 of the project, making changes to several of the packages and some tables.
-
A bug is discovered in one of the packages in Production.
-
Now what?
Hopefully you’ve been using source control and checking in regularly. If you have been using source control, you check in your current changes, perform a Get Specific Version to retrieve the version last deployed to Production. After you find and fix the bug – and test the fixes – you check in those changes and re-deploy to Production.
If you haven’t been using source control, you can export the project from Production and begin there.
There’s No Free Lunch
After checking in and deploying the Production fix you perform a Get Specific Version to retrieve the version of the Project that contains the code you were working on before you stopped to fix Production. And then you need to make a decision:
You are in an open field west of a minor version update to a data integration project. There is an SSIS Package here. Three paths diverge from your current location. The path to the north begins by manually re-applying the changes you just made to the SSIS package. The path to the south begins by manually re-applying the version 1.1 updates to the SSIS package you just updated and deployed to Production. The path east is marked with a sign that reads “MERGE CODE” but a hidden pit filled with hungry grues lies in that direction. </Zork>
- You can start with your latest version of the SSIS package and manually re-apply the fix code.
- You can start with a copy of the freshly-updated SSIS package (the one that had the bug previously) and manually re-apply the v1.1 updates.
- I recommend you not try to branch and merge SSIS packages in source control.
What’s Different with SSIS 2016?
SSIS 2012 and 2014 required you to deploy the entire SSIS project to the Catalog in order to update a single SSIS package. SSIS 2016 allows developers to deploy only the impacted SSIS package. You can now check in the current version of your SSIS project, grab a Production version of the single SSIS package from source control, fix the bug, test it, check it it, and deploy only that package to Production – without impacting any of the other SSIS packages in your SSIS project.
One thought before moving on:
There are two types of developers: those who use source control and those who will. – Andy, circa 2015
Demo
To demonstrate, I created a test SSIS solution in SQL Server Data Tools for Visual Studio 2015 (October release) in SSIS 2016 (SQL Server 2016 CTP3). I started with a single SSIS package named Child1.dtsx and deployed the project to the SSIS Catalog.
I next added another package named Child2.dtsx. When I right-click Child2.dtsx in Solution Explorer, there’s a new (and awesome) context menu item – “Deploy Package”:
As in the past, the integration Services Deployment Wizard starts:
The Review page indicates we’re only deploying the Child2.dtsx package:
I was curious about how single-package deployment impacts the SSIS Catalog’s notion of SSIS Project version history. I made a small change to the Child2.dtsx SSIS package and redeployed it alone, again. Viewing the SSIS Catalog History for the project, I see three versions. This matches my expectations:
- Initial deployment of the single-package solution;
- Deployment of Child2.dtsx package; and
- Re-deployment of the Child2.dtsx package.
Conclusion
I like the new functionality – a lot! Kudos to the Microsoft Integration Services Development Team for this nifty new feature!
:{>
I am here to help:
Contact Andy!
Learn more:
Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 – 10, 2015
Stairway to Integration Services
Stairway to Biml
Ok Andy, that new (and awesome) context menu item is useful if you have access to your production servers but what if you don’t? What would be the process for creating an ispac with just that one package and then handing it over to the deployment team? Is there an additional menu item for creating it? One of the things we have liked about having to deploy all the packages is that it is easy to know what has been deployed. I can easily look at my VCS and see via a label when we created our deployment ispac. The real problem I see is the extreme difficulty to merge code in a package. We always end up reproducing code in one direction or the other. If only we could write the packages in a declarative language they would be so much easier to compare and merge. Hmmmm, BIML?
Hi Steve,
Those are good questions. There is no requirement that anyone use the new functionality. The old deployment functionality remains and works. But there is no denying the ability to deploy a single SSIS package is beneficial in some scenarios, as I described.
:{>
I tested and verified deploying a single new SSIS package updates the ispac file, adding the new package.
I made a change to an existing package and then added yet another SSIS package to the project. I deployed the new package only. Although I didn’t deploy the updated version, the ispac file was updated with the latest version of the updated package and the new package. So, version dissonance is possible, but no more than in the past.
I’ve long maintained building SSIS packages is software development. In my opinion, we should treat it as such. That means versioning (labeling, striping, etc.), source control, change management, and all the other components of DILM (Data Integration Lifecycle Management).
Tools and utilities help. But tools and utilities cannot supplant a good process; rather, tools and utilities facilitate our processes. This is why I’m a fan of DevOps – especially in DILM. Let’s brainstorm the permutations, update best practices, document, rinse, and repeat.
:{>
That’s not a new feature, they just re-introduced the feature from 2005/2008 where you could also deploy individual packages 😉
there is no free lunch..
howmuch were you paid to write this and bloat about..
I agree with Koen Verbeeck, how does this differ from incremental deployments in 2005/2008? I will not be surprised if MS removes this feature in SQL2018 and reintroduces it again in SQL2020. Looks like they are running out of ideas.
Hi Koen and Gordon,
I respectfully disagree. I concur that the functionality is not new. The idea of single-package is certainly not new as you point out. Package deployment has remained supported throughout the SSIS lfecycle. And it was technically possible to achieve single-package deployments to the SSIS 2012 and 2014 Catalogs if one was willing to hack the ispac file some.
But I stand by the claim that single-package deployment to the SSIS Catalog – built into the designer – is new functionality.
A good question to ask is: “Is it *better*?”
I didn’t go into the negative use cases in this post, but they exist. As I stated, there is no free lunch; there are pitfalls with this new functionality.
There’s no substitute for good processes, and being able to deploy a single SSIS package is going to impact processes. Does that make single-package deployment bad? I don’t think so. Does that make single-package deployment good? In one sense, I think it does: Single-package deployment is another tool; another option that we have at our disposal. Like all tools (and options), they can be used for good or misused for bad. Given that, I prefer to have the option supported and built into the toolset. That’s merely my opinion.
I appreciate your feedback and comments.
:{>
WHERE CAN I DOWNLOAD SSIS 2016 FROM?
Hi Andy,
Thanks for your detailed articles, I’ve been reading your blog for months and loving it.
I love the idea of being able to deploy a single package, it makes total sense. However I’ve hit a snag.
I’m using the Project Deployment model, have Project Connections, and am pointing these to the correct DBs via expressions. The expressions reference Project Parameters, which are in turn populated by variables that reside in an SSISDB Environment and are then linked through the SSISDB Project configuration. (that’s quite a mouthful).
When I attempt to deploy a single project, it fails validation with ‘One or more selected packages are not ready. Review the Status column for more information.’
Here’s the kicker though, like the worst bug report ever written, in writing this to you I’ve found the fix!
It turns out the issue was caused when I had project variables that referenced package parameters. The fix is to delete these project variables, find all uses of them in the package and replace it with the logic that was contained in the variable.
Here’s a working example…
I had a project variable called User::CSVName with the expression: left ( @[$Project::Business], 3) + @[User::CSVSuffix]
I had a package connection called csvSource with a ConnectionString expression: @[$Project::CSV_Dump_Source] + “\\” + @[User::CSVSource] + “.csv”
This package failed to deploy independantly.
The fix was to delete the package variable, and modify the package connection expression to: @[$Project::CSV_Dump_Source] + “\\” + left ( @[$Project::Business], 3) + @[User::CSVSuffix]+ “.csv”
Following up, I’ve logged my first ever connect item on this: https://connect.microsoft.com/VisualStudio/feedback/details/3111664/failure-deploying-single-ssis-package-to-sql-2016-ssisdb
Hi Andy, I deployed the new package to an existing project in Catalog DB(2016) but can’t see the new package in Project at Integration catalog.
I deployed through ispac file
Although deployment of single packages is really cool for developers, it is 100% useless when you cannot build an ispac with a single package to hand off to your DBA to install in production. So although I use single package deployment during development, it provides no help for production implementation. I myself would rather Microsoft have just left this feature out if they weren’t going to actually provide full functionality only because its misleading to advertise single package deployment but then not include it in the standard packaging of deployment code which is the ispac.
I was wrong (dang thats the second time today, I mean this year)…
–> There is single package deployment within the wizard! It pays to check the obvious I guess.
Check out…https://www.mssqltips.com/sqlservertip/3676/sql-server-integration-services-2016-incremental-package-deployment/
When use the wizard, just change to package deployment and pick up the individual package from the file system. No its not using an ispac but its an acceptable workaround.