SSIS' 15 Faults
time to read 7 min | 1388 words

I dislike SSIS intently, and I say this as someone who has done two projects using it, and has spent much time recently struggling to work with it .Instead of harping how much I dislike, I decided that it would be better to list the things that I find it so hateful:

  1. Bad Errors: The number one reason that I am using .Net instead of C++ is not the garbage collection, it is the errors. Clear, understandable errors, with the ability to trace them back into their source. Errors that gives you the reason for what happened. SSIS' errors are anything but useful. Often you will get some sort of a cryptic COM error, and maybe you will get lucky and get the message from the database, instead of relying on SSIS's errors.
    The errors are not only hard to figure out, they are often hard to find, hiding in a mess of all the other information (mostly useless) that SSIS throws at you. No attempt is made to make it easier to locate and find the errors, those are left as an exercise for the user.
    The last time I felt this alone I was working with Pascal, trying to understand how the code moved the spindle of the HD using ASM commands.
  2. Random Errors: I like predictability. One of the worse things that can happen to you is that you get an "sometimes it happens, but I don't know why" error report. With SSIS, I literally had the entire project breaking up because I made such a significant change as changing the connection string. They are few things that I like less than having to deal with stuff that make me work in a stupid way, SSIS' way is to force me to go through all the boxes in my package and approved that I want it to do the same thing that it did before.
  3. Keeping track of what it shouldn't: I wish I had a dollar for every time that SSIS kept track of something it shouldn't. Be it the old configuration, hard coding the connection string inside the package and completely ignoring the configuration... etc.
  4. Sorry excuse for deployment: As you can probably see from above, SSIS's previous faults doesn't make for a nice transfer to production. I had to deal with that today, and it was a pain. Things just refuse to work if you move them between machines, when the database target remains the same. In most cases, those are "need new metadata" or plain "I am broke" errors, which leave you with the tried and true method of getting VS, opening the package, and starting to mess around with that manually, until it decides that it doesn't hate you so much again. Requiring VS to deploy successfully is a big mistake, but I have been unable to avoid this requirement in any package of any complexity so far.
    Would I need to do it again when I want to redeploy? Why, yes! It is not like I have better things to do...!
  5. Security? Who needs that: I should also mention that SSIS packages requires sysadmin rights to run when scheduled as a job. Which of course it will not tell you until you have run the job. I am aware of the agent proxy solution, it still remains a mystery to me why this is a requirement, after all, you can run an SSIS package just fine from inside SQL Server, just not as a job.
  6. UI formatting instructions along side with the executable code: Here is another huge mistake. The SSIS package contains both the executable blocks and the formatting for it, making it completely unreadable from plain text perspective, and making it impossible to understand what has change from a diff of the file.
  7. No thought about version control: As long as we touch that, SSIS packages and configuration might as well be binary objects, they are completely opaque for version control, and the decision to make the configuration files for SSIS un-indented was made by someone who really never had to modify a configuration file outside of the pretty UI, or wanted to actually see what is going to change.
  8. Bad configuration scheme: This bring me to the configuration scheme itself, yes, you can put the configuration anywhere you want, from incomprehensible XML files with hard coded paths to SQL tables to which you will have to have an hard coded connection string, from environment variables (WTF?!) to stored inside the package.
  9. Random configuration scheme: The problem with all those options is that SSIS seems to choose between those at random. At times it would choose the connection that it had assimilated inside the package, at times it would go to the wrong file, get the old configuration, or just complains that it is not having fun and that I should baby sit it again.
  10. Bad UI: If we are talking about baby sitting, that is what the UI makes me feels like. Having to feed it very carefully, in smaaaalll bites, what I would like it to do. Going through six dialogs and three property grid just to get something to work is not my idea of fun, and SSIS has the lovely errors to point your way. Oh, and there is the advanced property dialog, if I was feeling stupid.
    Then there are the worse issue, building expressions is horribly broken from the UI perspective, you can see the evaluated string if it is bigger than a few lines, and no one will help you if you dare break the source string into multiply lines.
  11. Lack of extensibility: My company has actually developed a series of components for SSIS, they cost a lot of time and frustration, so I can honestly say that trying to extend SSIS is nothing but pain.
  12. Bad interoperability: As it happened, I am dealing with Oracle. As an integration services platform, I fully expected SSIS to support this little known database, but it doesn't. I will spare you the pain of "CREATE VIEW Customers_SSIS_DOESNT_LIKE_ORA_TABLES as SELECT * FROM Customers;", but I assume your can guess the rest.
  13. Busy work: I mentioned it before, but SSIS is very click happy, requiring your to do a lot of work with the mouse, over and over again. Just trying adding a field to a data source, see how much pain you have to go through as you have to go through each and every one of the steps that it passed along the way, even if they never touched that field. Heaven forbid that you dare to remove a field. Did I mentioned the lovely huge dialog when SSIS basically tells you: "Nothing have changes, shall I map with the same column names?"
  14. Hard to debug: Today I had a dynamically generated SQL inside an SSIS package that was causing an error. I had no way of finding out how to get this dynamic SQL (which was stored in a variable), eventually I created a secondary path that saved the variable to  a table, where I could read it.
    Update: This is possible, see here for the details. The rest of this point still stand, though.
    Finding out why things are failing is a task for those who have little respect for their time, for SSIS insist that it is important enough to engulf all your time. Oh, and have fun getting a MESSAGE BOX from the SSIS process if a script task has thrown an exception! Obviously this message box will be hidden behind SSIS, so you will wait for the process to end for quite some time before realizing what have happened.
  15. The missing basics: I lost count of the many things that I consider a given that SSIS doesn't have. From date formatting to parameterized queries (to Oracle) to the basic UPSERT support. There have been a few times when I literally count not believe that it didn't have this capability. Date formatting and parsing is a basic part of what you ought to get out of the box with an integration package, as just simple example of a basic lack that is driving me crazy.

There are more, but I am going to walk the dog now.

Tweet Share Share 42 comments
Tags: