Recently I finished my first deep dive into using SQL Server Integration Services (SSIS) on a project.  This was the conversion of 88 fairly complex DTS Packages from SQL 2000 to 2005.  I've used SSIS numerous times in the past, but I still considered myself at the "beginner" level, I'd say i'm almost "Intermediate" now that I've completed this project. 

All of our SSIS Packages for this project were implemented on a separate layer from the dbms, ultimately allowing application administrators the ability to control the jobs (via windows task scheduler).   This is one of the greatest benefits of SSIS is the ability to separate them from SQL Server.  The three cons to this are loss of SQL Agent for scheduling,  SQL Licensing and Performance of some tasks (can't use SQL Destination).   The pro's out weigh these con's, and in the very few instances where a con can't be overcome we do allow the ssis package to run on the same layer (usually large loads from Oracle to SQL where the SQL Destination object must be used for performance). 

It is definitely nice to allow the administrator unfettered access to the box and they are completely responsible for developing, deploying, configuring and scheduling their own SSIS Packages, not the SQL DBA Team.  We have 4 SQL Server DBA's, 170 Instances; there are a lot more App/Dev labor hours than their our SQL DBA labor hours.  The App/Dev team loves it too, it takes them a bit to get used to it, but everyone of them loves it.  Make sure you have good SLA's as DBA's still need to understand when large loads and complex ETL's will run against a database.

During the process I compiled a list of "pet peeves" for SSIS, here they are.  Hopefully as we adopt SQL 2008 SSIS, i'll be able to cross some of these off.

• Excel (the list of issues seems endless)
• Columns longer than 500 Characters
• Strong data typing (Data conversion on 50+ columns is brutal!)
• Mapping columns after data conversion, ugh !
• Using variables for dynamic file names is not intuitive
• This is timestamps in filenames etc, once learned it's not difficult, but for others viewing the package the first time it can be convoluted to determine how the variables are used to manipulate the name of the file source
• Using wild cards for FTP (either put or get) is not intuitive
• Converting some previous DTS Tasks would perform a put (mput) of {timestamp}.*, this ended up either remaining a batch file in ssis or creating a for loop that actually performed individual puts (really not smart to login 10 times for 10 puts).
• Mapping columns in data flow tasks
• If the names don't match there is no option to just do a 1:1 in order match, frustrating and tedious when your dealing with 50 or a 100 columns to perform manually
• Configuration Files
• A must to implement, but just seems to have a high learning curve
• Restart of a failed packages (checkpoints)
• Just didn't work the way we expected, but once learned was quite successfully
• Oracle Integration
• The pain here was and is endless, 3rd party tool is really necessary if your dealing with lots of integration
• Default Properties for Fail Parent and Fail Package
• For us it seemed we always wanted this to true and the default was false, there seemed to be no way to globally change this
• Precedence Constraint Editor does not contain an expression builder
• Oh how I missed the 3 ellipses
• Executing another SSIS Package from an existing SSIS Package
• We have several "shared" packages, but we weren't able to use the execute package task due to limitations on changing the properties, so we ended up using the Execute process task, which calls a batch file with the proper configuration file...ultimately the execute package task was useless
• Readability of Configuration files makes editing difficult
• We end up using XML Notepad on most servers, it'd be nice to see the property name as part of the node or an element so one could visually scan down the side and find things quicker.