Matt,

Thanks for your reply.

Your approach to using DTS is the one that alot of people take, certainly I know I do. And my company do steadfastly.

You are right that I didn't cover the 'T' and I only really touched on 'E' & 'L', that will come later. In the meantime you may want to check out http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp or http://www.sqlis.com which has many articles already or my SSIS blog at http://blogs.conchango.com/jamiethomson which I put useful little ditties on now and again.

You still have the choice of using stored procs to do your work and indeed there is an ExecuteSQL Task in SSIS to enable you to do this just as there was in DTS. However, the rich transformation functionality in SSIS is a complete departure from the limited functionality in DTS. To give you a flavour of what transformations you have there are:

Sort - Sort data in the pipeline

Aggregate - Similar to T-SQL aggregate functions

Lookup - Similar behaviour to DTS Data Driven Queries but far far more performant

Conditional split - Split a data set into numerous smaller data sets depending on attributes of the data. Enables filtering.

Derived Column - Using the new SSIS expression language derive new values from existing ones.

Merge and Merge Join - Similar to T-SQL joins

This list basically reflects "standard" functionality is by no means exhaustive. There are many many more transformation components available for you to use in a data flow including things that have never een available before such as fuzzy lookups and fuzzy matchnig (i.e. Match inexact values based on a scoring mechanism - invaluable for deduping data)

I hope this is useful to you. Check out the above references and look out from more material from myself in the future covering some of these concepts in more detail.