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!
Hi Matt,
Thanks sharing the info!
I think having / forcing project development/deployment is great. It makes us think twice about how we organise our packages / projects in Integration Services Catalogs.
There are issues in regards to administering and executing deployed SSIS projects, which I have logged at Microsoft Connect. But they all have workarounds (albeit risky). So I am all for project deployment.
I am also keen to hear what others think on this matter too.
Kind regards,
Julie
I agree with this decision, but it's easy for me because I have a developer background and source control is as natural as compiling. However, for many DBAs-turned-ETL-developers, it's not. I think Microsoft can do a better job up front explaining that this is not scary and providing ramp up advice for those ETL developers.
In the SQL 2005/2008 world, a package could be considered similar to a standalone .exe file, or it could be a part of a bigger picture. You had the option. It was a logical thing, and not a physical thing. If an ETL developer wants to deploy a single package because they have to apply a patch, and don't want to affect other packages, or risk that any other dependencies are broken, what is the model? I don't think depending on source control is a good approach here, there should always be an option to inject a package into a deployed project. A full deployment model without an incremental one is not a good solution, in my opinion. Depending on the fact that users shouldn't do something due to best practices shouldn't stop a support manager from fixing a package in the middle of the night without breaking or having to redeploy the entire project, risking other packages to not run or fail.
Another way to look at this is from an Analysis Services perspective. Why do we require a BIDS (SSDT?) Helper tool to deploy an MDX Script, to prevent our cube from being invalidated? Why is this feature not part of the product?
To me it sounds like the argument that pie charts shouldn't be in a dashboard because they are misleading. Initially, it seems like this is a similar case of someone suggesting a feature shouldn't be there because it won't be used properly. It's hard to tell this to an executive who is used to looking at pie charts and can get them from a competing product. Hence the reason why pie charts are in PerformancePoint 2010, and probably why we should see an option from the community soon for injecting package deployments into a project.
A thread on forums shows the confusion this is causing for end users, though I haven't had to deal with this much personally just yet. http://social.msdn.microsoft.com/Forums/en-SG/sql…
Maybe taking a look at DataStage could assist with a deployment model. It sounds like you can replace individual assets within a project in their case. http://publib.boulder.ibm.com/infocenter/iisinfsv…
Another thing that bugs me is the fact that the Source & Destination wizards don't use shared connections… but that's a whole other topic, along with why the native sql driver is the default vs. ole db, and why new variables still appear scoped at container level rather than package level, and why SSIS still encrypts with user key by default.
I am glad package configurations have gone away… or have they?
As a former .NET developer I strongly disagree with Andrew Sears and find the analogy “.dtsx is source code” (and must be in a source control system) absolutely right. It's very important that development environment and the whole infrastructure encourage best practices and discourage "other" practices. The second point is almost as important as the first one. I think the C# and BCL Teams did a great job promoting the best practices, and the same road should be taken by BI-Teams at Microsoft (SSDT/SSIS/SSAS/SSRS etc.).
There is a way to deploy a single package: export the project from SSISDB, replace the package, redeploy the project. And my belief is that this is exactly the way it should work.
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.
I think you could import the project into Visual Studio from the server, then replace the package, then redeploy.
More details http://msdn.microsoft.com/en-us/library/hh270294….
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.
Sorry, I can't seem to edit the comment above, by "master package" I meant "master project".
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.
Lots of great comments here!
Personally, I do like it when a product provides multiple options on how to do something. This is one of the reasons I originally pushed to support “incremental deployment”. Unfortunately, as anyone who has worked on software projects before knows, we don’t have unlimited resources, and aren’t able to implement everything we want to. Incremental deployment is something we could support in the future (we do listen to Connect items!), but what I wanted to get across in this post was that most of the customer requirements I’ve seen for this feature could be better met by using source control and other team development best practices.
To address some of Andrew Sear’s comments above:
- I think the Source/Destination assistants could provide the option to create those connection managers at the project level, but I don’t think this should be the default behavior. Note that you can convert a package connection manager to a project connection manager on its right-click context menu.
- Package variables are created at the package level by default in 2012, not the currently selected container
- We’ve gone back and forth a number of times on the default Protection Level value. It’s definitely not ideal for team environments, but for the majority of our users, user key encryption is the easiest thing to use. Actually, the majority of users don’t have packages that contain sensitive fields – I believe most use windows authentication for their connections. The automatic encryption settings in the SSIS Catalog make this less of an issue than it was in 2005/2008.
Hi Matt,
Thanks for the reply. I noticed that the source/destination assistants don't let you pick a shared project connection which is what I meant. I don't use them anyway but for those that would it might get confusing.
When I create a new variable using the pick list inside a component instead of clicking on new variable in the variables window, it seems to scope it at the component level instead of package level. Probably by design…
The default protection level thing doesn't usually come up that much as I always set to don't save sensitive by default.
One item that would be of use for those using Oracle would be a way to filter by schema before having to pick a table for a source/destination component. Where I am we have 18,000 tables and views. Not sure if there is an option to do this. The data source view in SSIS 2005/8 was a good idea though it didn't get much traction.
cheers,
Andrew
Thanks for the very absolute and clear post Matt.
Now let me try to explain my work environment so that it doesn't sound as ad-hoc as it feels sometimes.
I work in the IT Business Applications dept of a very large oil and gas company; we are not an ISV and all our projects are for internal use only with typically very small or specific scope. I've been able to get the older developers to enter the 21st century and use source control to an extent but there is constant grumbling of why things can't be more like they were with DTS and SQL 2000 where you just saved the package and that was it. I'll admit also that all of my development experience has been in database design (starting with SQL 7) so I have not had much exposure to conventional application development and source control within a structured development environment. We won't even talk about the concept of branching and merging since that would be going off the deep end.
One of the biggest ongoing projects I work on is our corporate Data Warehouse. We import all sorts of data from hundreds of different ERP systems across the company. We have grouped our SSIS packages into projects based on scope/destination within the DW such as GL, PO or Inventory. Each project contains dozens of different SSIS packages for importing data from each of the individual ERP systems (some may be Oracle sources, flat txt files, SQL Servers, web services etc.).
At any given time we may be working on a half dozen different packages within a project (upgrading logic, adding news ones, one-off fixes) while the others work fine. Since these packages aren't part of a singular process but rather individual feeds into a single end result how would we finish work on one package and then deploy it to production without having to maintain separate projects for each ERP system and destination?
[...] What is your take on this? http://www.mattmasson.com/index.php/2012/07/can-i-deploy-a-single-ssis-package-from-my-project-to-th… [...]
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