Jeff Moden (3/21/2013)
david.alcock (3/21/2013)
SSIS is normally utilised when the copy/move is complex, if it involves data transformations, validations etc.I'll go ahead an disagree with that. I've built some killed ETL systems that would logon to FTP, download only what was needed, mark what was downloaded, import the data, transform it, validate it, and merge it with the final tables all using just T-SQL including some rather crazy "don't know how many columns there are in the import" functionality using data imported from Double-Click.net.
ETL is just not that difficult. It's ok to use SSIS for this type of stuff but I haven't actually found a need for it. I've even been able to do parallel loads without it.
When a guy like Jeff starts off with saying 'I'm going to disagree with that' you know you're in trouble, well I get that feeling of dread anyway!!!! 🙂
Of course you're absolutely right, you can implement all kinds of magic using T-SQL. My suggestion was more based on the requirement here and not a generalisation, if it involves some level of complexity then SSIS would be a way of implementing the process rather simply (like a lookup task or something).
'Only he who wanders finds new paths'