I have some experience with DTS and have walked through some SSIS tutorials in my current data warehousing class. I have taken on the DBA role and want to revamp our whole ETL process so that the same genearal approach is used for all uploads of data into production. Right now, each upload has its own process usually with some analyst uploading data to a holding table on production SQL Server using Access! Then a variety of scripts are run in Management Studio. I'd love to hear from the trenches before I tackle this project. Thanks!
Use your ETL tool for E, and for L, but not for T.
Drop the data into a staging table with absolutely no constraints, so that it is nearly impossible for an error to occur at that point. Now, your debugging is limited to a single stored procedure where you verify the staging data, cleanse it, and distribute it to production tables.
Toss an "ImportDate" column on the end of your staging table that defaults to the current date and time, and an "ImportSource" column for storing the name of the file from which the data was imported, and finally an "ImportError" column where your stored procedure can note any issues that prevented the data from being distributed to production tables. Then you have a robust process that can accept data from multiple sources, and maintenance and troubleshooting is pretty straight-forward.
If it's not practically useful, then it's practically useless.