SSIS reminds me of Crystal Reports – it works well for the simple cases and does have tools to allow complicated options. However the complex parts become very fragile.
For crystal reports I eventually took to putting as much of the logic as possible into a view or a sproc so that crystal only requires a simple select plus a few formatting options. If you treat Crystal as a presentation layer you get two benefits – far less debugging plus a possibility of moving to a better option when one becomes availbale.
SSIS is similar. I was having a lot of trouble coping with moving unicode data around. The best solution that I could come up with was to alter the select commands to cast the data to the right type. SSIS can perform automatic name matching if the column names only differ by case.
My transformation goes:
Select from Sybase -> Staging Table
Call Proc that updates/inserts the real table from the Staging Table.
How much is SSIS actually buying here? A custom app could do all this and I could define the mapping in a seperate table (which I can’t do with SSIS). In addition the error handling in SSIS is a bit of a joke. Eventually it comes down to using the profiler if a problem is detected.
Developer Rants 