SSIS: The Ninth Circle of Merge Hell

If there is such a thing as divine retribution, then I’m pretty sure that most of the people on Microsoft’s SSIS product team will be spending eons merging SSIS packages.

I took the following screenshot from an issue I reviewed at my old job. The developer who resolved the issue only made two property changes and realigned the boxes in the visual designer (can you say OCD), so I expected to see a small diff but instead saw this unholy mess.

SSIS_Merge

For those of you who are not familiar with BeyondCompare, all those red bars on the left represent changes to the file (100’s of lines).

Since only two meaningful changes occurred in the revision, that means that the vast majority of it is noise related to tracking the positions of all the elements rendered by the visual designer.

Is the visual tooling here so essential that it is really worth losing the ability to track and comprehend how the code has changed over time?

What about the ability to collaborate or work in parallel on tasks?

At my last job it was extremely common for devs to work on different features at the same time that touched the same code files. Unless the exact same lines of code were changed, the devs were able to merge these files with little or no effort.

However, when it came to SSIS packages we were forced to ditch the last 10 years of software engineering best practices and instead expend vast amounts of effort in planning and endure ridiculous amounts of inefficiency just to ensure that no devs even thought about touching the same SSIS package at the same time.

If the dreaded merge window did ever appear during the check-in dance on an SSIS package, devs habitually ditched the merge tool and instead relied on the extremely tedious and error-prone approach of manually reapplying all the changes through the visual designer.

I realize that there are quite a few developers who work in small shops and don’t have to deal with scenarios involving extensive collaboration and parallel development, but what about the case of a hotfix that happens in the middle of a multi-week project?

With a normal code file you would be to follow a simple mainline-release pattern, make a change to the release branch, then merge back into the mainline branch.

However, with SSIS the only alternatives are to either lose work, try to do some wonky work-around with a data load, or else just throw throw yourself at the mercy of your boss and try to convince them to delay the hotfix until the larger project is done.

Did nobody on the SSIS team consider these issues when designing the product or was SSIS really just meant to be a basic end-user tool like Access?

Perhaps this is one of those situations like with VSS where everyone who actually works at Microsoft uses some other ETL tool that allows them to follow basic modern software development practices.

Then again, maybe the development community as a whole has just become so blinded by the desire and expectation for visual tooling that it trumps all other considerations.

Whatever the reason, there is no way I would ever voluntarily use SSIS again.

Does anyone know of any more source control friendly alternatives?

45 Comments

  1. Moti June 16, 2011 3:34 am 

    As for alternatives, I don’t understand why the previous commenter did not mention it, but ayende has the rhino-etl tool, which is as source control friendly as it gets…

    No fancy designers, however.

    http://hibernatingrhinos.com/open-source/rhino-etl

  2. Neil Barnwell June 16, 2011 3:51 am 

    I had a training course on Workflow Foundation, and it seems that has a similar problem – merging changes for the “xoml” for a workflow is a serious headache.

    When designing textfiles representing “code” for people to write applications, this should be right up there on the priority list. Stuff like this happens when people developing it use VSS for source control…

  3. Russell Ball June 16, 2011 7:48 am 

    @Ayende – Awesomely comprehensive list. I remember reading that post when it first came out, but I didn’t have to deal with it at the time so it didn’t fully sink in.

  4. Russell Ball June 16, 2011 7:53 am 

    @Moti – Thanks for the suggestion. I remember reading about that on Oren’s blog a few years ago, but wasn’t sure if it was being maintained. The page actually looks like it has some decent examples. I’ll have to try it out.

  5. Paul Johnson June 23, 2011 2:01 pm 

    Hey @Russell – After spending some significant time on SSIS over the last 2 years, I absolutely concur with the version control issues that you are having. Our team turned off multiple check-outs, but then that caused a whole other set of problems when you were simply trying to open the file for review. Just the mere opening of a data flow would cause an attempted check-out, and since the second person couldn’t check it out – it was almost un-usable. So, we turned multiple check-outs back on, and PRAYED that we all communicated enough to know when it was OK to make a change.

    Unfortunately, one of the guys on my team was correct when he said “SSIS is a piece of S__T, but it is the best piece of S__T out there”

    I’ll have to look at @Ayende’s list and see some of the alternatives.

    BTW…hope you are liking the new job!!

  6. The Phantom February 1, 2012 1:33 pm 

    You had it exactly right in your article. SSIS is an end-user tool like Acces. Anyone who is an experience SQL ETL developer knows this. Everyone else assumes that SSIS is the way to do ETL. I have over 18 years of experience with TSQL and I just have to quietly laugh inside whenever some naive employer or big-headed idiot trys to tell me there’s some good reason for an experienced SQL Developer to use SSIS. SSAS is essentially the same, an end-user tool for those who don’t know how to do ROLAP with TSQL. 90% of people who read this will think I’m wrong, the other 10% are experienced SQL developers who know what I know.

  7. Wes February 20, 2012 9:15 am 

    @ThePhantom. Please don’t insult Access by even using it in the same sentance as SSIS. For all it’s problems Access is a masterclass in how to produce useable software when compared to SSIS. I can’t think of any product I hate more that I’ve used in the last 15 years. Everytime I have to deal with it, I know I’m going to have a bad day…. Really I’m ready to kill someone, something right now!

  8. essayswriter.net March 1, 2013 8:48 am 

    I really loved reading your blog. It was very well authored and easy to understand. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he enjoyed it as well!

  9. www.academia-research.com March 5, 2013 5:16 pm 

    Very fascinating article. it is amusing how backdrop could be twisted in so an enormous amount of a range of ways.

  10. http://idealessay.co.uk March 25, 2013 2:58 am 

    I really loved reading your blog. It was very well authored and easy to understand. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he enjoyed it as well!

  11. master-essays March 26, 2013 6:14 pm 

    Well written great! Thx!

  12. courseworkhero April 3, 2013 11:46 am 

    That article is so interesting and makes a very nice image in my mind. Thank you for sharing this info with us!

  13. http://bestessay4u.com/ April 8, 2013 7:34 am 

    Nice effort, very informative, this will help me to complete my task. Thanks for share it keep it up…

  14. essayforce.com April 9, 2013 12:54 am 

    Interesting…thns for posting!

  15. turboessays.com April 21, 2013 12:39 pm 

    thanks a lot for the post! very interesting!

  16. wedoessays.com April 22, 2013 12:22 am 

    Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me.It’s always nice when you can not only be informed, but also entertained! I’m sure you had fun writing this article…

  17. http://termpaperwriter.org/ April 25, 2013 2:17 pm 

    Nicework!! I always visit your site and I like it! There is a lot useful info) Thanks!!!

  18. writingbee.net April 26, 2013 7:19 am 

    Good job. All of them are useful.

  19. termpaperwriter review April 26, 2013 8:29 am 

    I really loved reading your blog. It was very well authored and easy to understand. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he enjoyed it as well!

  20. http://order-essay.com/ April 29, 2013 6:30 am 

    I don’t have husbandnow, but it would be useful for me in future! Thanks!!

  21. bestessaywriter.net May 14, 2013 10:17 am 

    Interesting theme! I ll discuss it with my friends.

  22. anydissertation.co.uk May 18, 2013 6:00 am 

    Thanks for choosing this subject! This post is very useful!

  23. bestessaysonly.com May 22, 2013 6:02 am 

    You interested me with this post – I should admit! Good work indeed! ))

  24. bestessay4u.com May 31, 2013 8:04 am 

    As for me, it is very interesting post! Good job! I like to read more posts from you.

  25. http://writingfavor.com/ June 12, 2013 9:41 am 

    It is very interesting to read! Nice work! I ll share this site with friends!

  26. http://bestresearchpaper.com/ June 22, 2013 6:11 am 

    Nice Posting done by you. i always find you your blog intresting.keep on posting such nice things

  27. Buy DVD Drives September 30, 2013 11:40 pm 

    Your blog was really wonderful! A great deal of ample assistance along with inspiration, both of which we all need!

  28. Stryker Lawyer News November 19, 2013 8:48 am 

    I am also researching for control-friendly alternatives for SSIS. I will immediately hit you up if I found some. Great blog, by the way. Keep posting!

  29. New Year Wishes December 17, 2013 10:56 pm 

    Great blog, I really love how it’s easy on my eyes and also the info are well written.
    I am wondering how I may be notified whenever a new post has been made.
    I have subscribed to your rss feed which should do the trick! Have a nice day……

  30. homemade gift ideas for men February 14, 2014 12:11 am 

    good posting and very nice article, this site give me interesting information.

Leave a Reply