No. No you cannot. In SQL Server 2012, you will always deploy the entire .ispac file to the Catalog.
I’ve received this question a number of times in the past couple of weeks. It’s actually a feature we considered, and one that I (initially) argued for, as I know it is a common practice with SSIS solutions built for SQL Server 2005 and 2008. However, I was quickly convinced that the scenarios that require single package, or incremental deployment of a project can be solved using team development best practices and procedures. The central one being source control management of your SSIS packages.
Let’s compare developing an SSIS solution to developing a C#/.NET application.
- A package (.dtsx) can be thought of a source code file (.cs)
- The project (.ispac) can be thought of as the binary the source code compiles into (.exe)
If you have a team of developers working on the application, you would not do the following:
- Place the source code in a common directory which all developers work on simultaneously
- Update individual source files without recompiling the project binary
- Commit partial changes to one source file when it could break code in other source files
- Build and deploy the project to a test/integration/production environment when you are unsure whether the changes to other parts of the code made by other developers are complete
(Ok, maybe would not is too strong – I’ve definitely seen all of the above done before . How about we use should not instead?)
When I ask for details about the scenarios that people require single package updates for, it typically turns out that they are doing one or more of these “should not” things in their environments. If all of these things are red flags for application developers, then why do people do them with SSIS solutions?
I described some ideas I had for SSIS branching strategies when you’re using source control a while back. I’d like to add the following high level recommendations to that post:
- If you have a team of developers working on SSIS packages, you should put you SSIS packages in source control
- Developers should not commit changes until they are complete, or in at least in a reasonable state where the project is in a working state
- If you have developer working on a very large change that could potentially break things (or one you want to do in multiple steps), do the work on a sub-branch to minimize impact to the rest of the team
- Builds that get deployed to test/integration/production environments come from stable branches, not from the development branch
Now, there are probably cases where single package updates for an SSIS project deployment can come in handy (even when you’re using source control, producing regular builds off the integration branches, etc). I just haven’t been able to think of any. If you have one of these scenarios, I’d really like to hear it – either via Connect, or by posting here (or ideally, both). So far the customers I’ve worked with found that these deployment requirements went away once they started adopting some of these application development lifecycle best practices… but I’m always happy to be proved wrong!
I agree, DTSX is just source XML code, and the workaround to deploy a single package is fine by me. My comment was around the support and mantenance of project vs package solutions and the lack of supporting features around the project solution.
I do think best practices are important to enforce, however best doesn't necessarily mean always and there are certain scenarios where this would just bring up questions with my clients as to why the single-package feature is not included in the product. Especially if it is one of the most common developer tasks. Exporting the project, replacing the package, and redeploying the package could be mitigated by an option to deploy a single package. Less steps, less chance of messing things up. However, if this feature was a tradeoff between exposing parameters or shared data connections or data taps or dashboards in the current SQL 2012 product, I would gladly toss it out the window if there was a workaround.
I like the idea of a project-based deployment model provided it is scalable, performs well, and is easy to support and maintain.
Hi Andrej, we're testing various deployment scenarios in SSIS 2012. Could you give me a bit more details regarding this suggestion of yours: "There is a way to deploy a single package: export the project from SSISDB, replace the package, redeploy the project"
Exporting the projet from SSISDB creates the "ispac" file but then:
– Where/how does one replace the package?
– Do you redeploy from the ispac file above?
It's late in the day so I'm hoping someone could spell it out please 🙂
Hi Johann,
Andrew Sears have answered the question "how". Concerning my personal experience using this feature – I don't have any 🙂
Our SSIS packages are always first checked in in some source repository (depending from client – SVN, TFS, Mercurial etc), so a situation "Something was deployed and the sources cannot be found" is non existant.
Our deployment processes are also usually structured in such a way that we pass some kind of deployment unit to the test team and after successfull testing we use the same deployment unit (with another configuration) for the production. In a simple case this would be a .zip file with db scripts, new version of ssis packages +deployment manifests (2005/2008), new report definitions etc.
Same for our development environment (continuous integration). We don't deploy just the packages that were changed, we deploy everything and let our unit tests check that the data after the massage in the ETL-process is exactly what we're expecting.
.ispac is pretty much a compressed folder with .dtsx files in it. You can open it with any compression tools and replace the package you intended to "deploy" after it has been modified. However, I strongly agree to the point being pushed here – there is a reason why you cannot/should not deploy individual packages. It helps maintain the link to the project/solution file, lesser chances of orphan/misconfigured projects and code versioning (via SourceControl tools). I believe the fear is that an unintended package change to another package or a configuration miget also get deployed. However, that again brings us to usage of Source Control tools, proper check-out/check-in/shelving policies (I'm a TFS guy!) and collaborative development techniques. If I were to vote, I'd choose not to have a "deploy package" option due to the sheer unknowns around it.
Okay, I now see the thinking with this approach. I think our concern is mostly around warehouse maintenance once in production. We also use source control (TFS) to safeguard our work but a question was raised:
"What if a production job fails at 02:00 in the morning and the person on standby needs to fix it quickly to get the batch through. Our project in TFS project is in development so contains a bunch of unfinished/untested packages. The persons on standby would not want to deploy the entire project but only the problematic package"
I'm thinking of creating a project per developer which they can use for development and only once they are happy that the package is 100% correct can it be moved into the "master package" that gets used for deployment. This way we at least know we can never deploy half finished packages etc to production. I suppose this kind of approach supports Andrej's suggestion of only ever deploying that which is tested and ready. Many ways to skin a cat I guess.
Thanks for the replies guys.
This is source control 101. You check builds in and you label them. If a hot fix needs to be made you cna check out from that label and make teh change. Then the fix is merged back into the development branch.
Source control methods may mitigate this master project approach by having a labelled version for production. Keeping backup directories for different deployed versions could help though it would be another process to maintain. In some cases that I know of, support teams wouldn't have access to either SSDT or source control so file backups could be a mitigation for that. It would be nice to be able to roll back versioned packages or import external packages into the SSIS Catalog project.
As Matt and Andrej suggested, it is really a source control / process issue but for smaller or less process-oriented shops project vs package deployments could get confusing. And what to do if the DBAs don't use source control, as a couple of my clients are in this situation?
Would like to hear comments on the master project approach suggested by Johann as I have a meeting Friday to discuss advantages of implementing SSIS 2012 vs. using Oracle PL/SQL for all ETL processes. I have around 30 so far… 🙂
The SSIS Catalog does support rollback, but again, it's at the Project level… you can roll back an entire deployment. I believe we keep the last 10 project deployments around by default. I wouldn't recommend this as a source control replacement (have you seen the new cloud based TFS stuff at http://tfspreview.com/? It's pretty easy to get up and running), but it's ok for basic scenarios.
Thanks Matt, great post about this issue.
My personal take on this is that SSIS shouldn't be limited in features because of someone's idea of "best practice", but instead it should leave it open to the developers and complement how they currently work., rather than enforce further restrictions on their process.
Aside from the numerous times we ended up with conflicts due to multiple people working on different packages within the same project, deploying the entire project every time (and particularly with large projects/busy catalogue) can cause Timeouts to happen, leading to failure in deployment, this can persist for hours before the catalogue is able to receive new packages, I believe deploying single packages will lead to a lower chance of something like that happening.
I love the new SSIS, but in an enterprise environment and when you have hundreds of packages on a catalogue, I believe the new model is not up to the task and still suffers from many draw backs, I cant even run the SSIS maintenance job because it takes about 20 hours to run blocking every single package in that catalogue.
Anyway rant is over 🙂 Thanks again for the article Matt
This is a nice feature, IF you really really like waterfall project methodologies and do NOT want people to adapt agile project methodologies.
A project is in this respect NOT a compiled exe-file, but a set of related exe-files.
As we – and many others – try to work with short sprints and incremental updates, this "feature" is really a pain in the ass.
And yes, we do use source control and what not.
M
Hi,
A scenario,
One/Multiple person(s) work(s) on a SSIS 2012 project between 9:00 – 5:00.
One/Multiple person(s) work(s) on the same SSIS 2012 project between 5:00 – 1:00
One/Multiple person(s) work(s) on the same SSIS 2012 project between 1:00 – 9:00
Source Control is not being used.
These dev's could be working on same pkg or different pkg.
Working copy of the project is already deployed on the server
New pkgs are being added every now and then.
Developers are unaware of the changes made to other pkgs.
Your pkg needs to be deployed.
What can we do now?
We don't want to deploy the project as other pkgs might be work-in-progress.
A work around that I can think of.
Right after the working copy of the project is deployed to QA/Production, save that project as a master copy. ("Master Project")
Create a new project which is a copy of this Master Project ("WIP Project").
Have developers create/modify pkgs in the "WIP Project" version of the project.
After each developer is happy with their work on w/e pkgs they are working on, copy-&-paste/replace that pkg into the "Master Project".
Deploy the "Master Project" to the server.
This way, you are,
a) deploying all pkgs
b) deploying updated version of your pkg
c) deploying working version of other pkgs that you have not touched.
“Master Project” & “WIP Project” can be created any time as long as all pkgs are working 100% at that point in time.
Do you guys see any issues with this?
Thank you
I like the flexibility of the package deployments and having the ability to move very specific updates into an environment quickly, however I do acknowledge that it comes at a risk. I also like the thought that a project is an entire application, which functions as a whole. One question that I have around the project deployment model is the relationship between a single package and the underlying schema. I work on a large Data Warehouse project and we frequently have our source controlled SSIS project build and validate. At the same time our DB project (schema) will also validate, however, there isn't an easy way to determine that the changes for a package are in sync with the database project, except to deploy both to a server and run the ETL. At this point the idea of deploying 300 plus packages to an environment (project deployment) to support the active development of 15 packages is slightly daunting. Is there a recommended best practice for keeping the SSIS and DB projects in sync?
I am a sole developer with about 15 packes In single SSIS project on SLQ 2012. I use TFS2010
I'm currently creating new packages but also get asked to make changes to existing packages.. My packages and project are in TFS.
Without creating a branch how can I deploy the quick fixes without deploying the long term fixes which aren't ready to be placed on the sql server ? As far as I know i can't deploy a specific version from from TFS directly.
That's correct. The current design requires that the entire project be updated. It's recommended to adopt a branching strategy for the scenario you describe.
I noticed this thread is old, but I wonder if this deployment model has changed by now. I'm sure there are valid scenarios when single package deployment is needed. In our case we have a master package with the common functionality of our load. This executes only one sub-package (selected out of many) that handles a variable piece (mapping of the source schema to our common target schema). We have hundreds and and soon anticipating thousands of source schemas, each with its corresponding sub package. We process thousands of files during the day for all of these schemas. Our business users have a tool to manage and define this mapping when they onboard a new source, and this automatically generates an SSIS sub-package to be executed by the master package. (BIML anyone?) Once they are done they need to deploy this sub-package. Now what… I'm still researching options. I don't want to re-deploy hundreds or thousands of packages that didn't change, and most likely are in the middle of an execution just because a new sub package was added to the pool. Unless the master can be on project deployment and the subs can each be on their own project, file system or store, we can't use project deployment at all. I personally thought of not using child packages but a single fixed package with a scripted component to resolve the variable piece with a rules engine (business users will modify the rules, no sub needed, master package executes the rules using WF rules engine and rules editor) but this design was not accepted. Both approaches are valid and they both have their challenges and merits, but generating sub-packages to plug in at runtime was the selected way and I need to resolve deployment, and I can't see the big-ax-monolithic mentality of the project deployment working for us. All suggestions are welcome.
That's a workaround which is NOT SAFE in projects dealing with 10s & 100s of packages. Any mistake by any developer / wrong version checkin-checkout will cost organization handsome in term of data corruption, business loss, etc.
Plus you are adding to the cost, you need a well disciplined version control & Testing processes in place and the source control software – which again need experienced people resource. Not every shop is equipped or afford it.
Has MSFT got this feedback (any connect link?) and working on it in future release or patch for incremental deployment feature?
Horrible decision based on a perfect world scenario which cannot be guaranteed. Never close your tools off in such a way.