SSIS is a great tool, and I really like some of the things that it allows me to do. But for crying out loud, you can't debug this thing. The errors it generate are reasonable enough if it is SQL server errors, but if it is something that is generated by SSIS itself, god forbid that you'll try to get what the error was.

Here are some examples for things that really frustrate me:

• 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.

On the other hand, when you actually manage to run it, the operation itself seems to be very quick. And the UI is in shoothing pale yellow that is easy on the eyes.