Ayende @ Wiki

I dislike SSIS intensely, 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:


Things that I personally found horribly broken:

  • You can't close a package after execution. You've to manually stop debugging and then close the package.
  • No way that I could find to look at just the errors/ warnings from the log.
  • The Flat File Source just blithedly ignored the fact that the file doesn't exists and carried on its work. I need this behaviour in several places, so it would be useful to do it, but I've no idea why or how this works.
  • You can't edit connection managers without a live connection to use.
  • You don't get any results when you search for the errors on Google.
  • 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.
  • 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.
  • 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.
  • 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...!
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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?"
  • Hard to debug: 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.
  • 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.
  • When you get a failure in the data loading, the error code you get refers to the LineageID of the column that caused the failure. The LineageID, as far as I could figure out, is some random integer that is attached to a column on creation. Why this is the error information that I'm getting, I'm not sure about.
  • No way to take a formatted string and turn it into a date unless it is dd-mm-yyyy. It doesn't accept any other format.
  • Speaking of which, it insists on using DB_TIMESTAMP for datetime columns in the SQL Server.
  • The UI to do complex stuff is just plain horrible. You need to do some transformation on a column? You get to write something that looks like a hybrid of the worst stuff in both C and VB, in a one line UI, that has major scrolling problem.
  • When the UI is generous enough to gives you errors, it will display them when you hover over it, for exactly 3 seconds, and then you need to move the mouse so the hover event would fire and then you've another couple of seconds to read the error.
  • Finding out what failed is a matter of trail and error. I had a problem where I had a date formatted as dd-mm-yy, instead of the yyyy that it expects. It worked like a charmed in that task, and then bombed on me when we tried to save to the database with a completely obscure error about not being able to convert timestamps to datetime.
  • You tend to get disk I/O buffer errors occationally, I'm not sure why, trying to run it again usually works.
  • It's really annoying to put it into a source control like VSS, since it set the file state to read-only, which cause SSIS to throw errors and means that if I'm testing a package, no one else can use it.
  • Very limited support for doing things across multiply packages.
  • The UI is sloooow at best.
  • The UI is also obscure as hell, just try to do a pivot table in SSIS. Give me a apache.conf any day, at least that is documented.
  • Some basic operations are hard to do. So hard that I wasn't able to do them, in fact. Getting a value from a stored procedure into a User::Variable is one of them.
  • Did I mention that I find the UI extremely awkward to work with?
  • Copy & Paste takes an exteremly long time, and often produce unpredictable results.
  • Limited ability to write actual code. This mean that in order to solve a very simple problem, I need to either add an assembly to the package (add a script task in VB.Net only, appernatly {which I then would need to share between packages, meaning copy & paste errors}) or do it in some rather nasty macro langauge that I really don't understand the benefit off.
  • Horrible debugging experiance, if something goes wrong, unless you you knows what is wrong (and we found some fairly typical errors that we made), you're screwed. You're left to try this and that until it works or gives a different error message. No way that I found to step through the actions as they occur. (And yes, if in the middle of 1,000,000 records load I get a bad record, I need to be able to see what is wrong with that record. Or all the nine other records that precede and follow it.
  • Try-finally is hard to do. I may have Pre Action, A,B,C Post Action. The Pre & Post Actions need to be executed regardless of what happens in the middle. That is not trivial to do in SSIS, even though it should. I may be able to do so if I put everything in a sequence, and then put the post action as the next action with execute on completion, but that doesn't work if I have a transaction already running (and you can't put two sequences inside one another and then chain stuff inside of them).
  • Exporting / Importing data from SQL Server to Access and back (same schema in both SQL Servers, empty MDB file to start with) is not working. For some reason Access is using memo (NTEXT for SQL) instead of text (NVARCHAR for SQL), which breaks the import process. And that isn't easy to find either, it just gives a general error about data type mistmatch in a column. Which column, it doesn't bother to tell me.


Things that people on my site hate about SSIS (and I agree)

  • No throttle control. It does allow you to set some thread settings, and write out files to a temp path as it does what it wants, but I had cases where it would absolutely swamp a well-built dev machine so much that the machine became absolutely unusable as it ran.
  • And then 35% of the time, it would fail. Silently. Not even bad errors. Just stop.

Adam Machanic:
  • Ooh, and I want to rant a bit more! Create a package that inserts some data into a table with a VARCHAR(15) column. Then go into the database and change the column to VARCHAR(20) and YOUR PACKAGE WILL BREAK!!! Arrghh...
  • And if you want a really fun day, try changing a connection manager from SQL Native to some other provider. Oops, you can't--it's greyed out. So try deleting the connection manager, then re-creating one with exactly the same name but a different provider, and... You'll discover that everything internally is referenced by a GUID, and your entire package is broken.

adolf garlic:
  • copy/cut/paste causes random serialization errors
  • can't copy connection managers with their tasks
  • connection "managers" cannot cope with simulataneous multiple data flows
  • vs.net just blows up for no reason
  • sluggish due to constantly trying to validate everything all the time
  • piss poor excel integration
  • no upsert task/component
  • stupid proprietry datatypes
  • terrible navigation experience
  • crappy expression language "similar to c++" and editor
  • overly verbose but unhelpful error handling
  • hyperlink error references to the ms website which mysteriously do not exist
  • you end up working with a "what the hell buttons/checkboxes things do i have to delete, remove, add in order to get this frigging dialog box to feck orf?" mentality
  • unable to properly preview excel "too many fields"
  • "suggest types" correctly identifies dates, then claims that they are not dates/wrong format error
  • have to click on about 5 things to physically view the script in a script task (hello microsoft, the clue's in the name!)
  • crappy column mapping drawy liney thingy which is tedious in the extreme
  • load packages is slllllooooooooooooowwwww - makes windows 98 look good
  • "turd on a stick" sql editor

Joel Mansford:
  • with Oracle it's impossible to get data in to or out of MySQL using SSIS, even with every .NET data provider under the sun.

  • create a lot of variables and then realise they all have the wrong scope. Can you change it? Nope! Recreate or poke around in the xml

ScrewTurn Wiki version 2.0 Beta. Some of the icons created by FamFamFam.