david.alcock (3/22/2013)
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).
Heh... sorry, David. I didn't intend to make anyone nervous. Like you, I was making a mostly general statement. I'm just amazed at how many people make it seem like you have to use something like SSIS to do even the simplest of IO tasks. Perhaps the greatest irony of them all is the fact that many people will call a stored procedure from SSIS to accomplish their SSIS task but will still insist that SSIS is the only way to go for "complex" things.
--Jeff Moden
Change is inevitable... Change for the better is not.