opc.three (3/21/2013)
In this case SSIS is a good choice regardless of the complexity of the tranformation because the source and destination databases are going to be on different instances.If you want to stay in T-SQL your choices become Linked Servers or xp_cmdshell. Personally I would continue down the path of implementing an SSIS Package to do the archiving.
I have to both agree and disagree there. It's no more difficult to setup a linked server than it is to setup an extra connection in SSIS. So far as importing data from a non-database source on another server goes, there's no need for either a linked server or xp_CmdShell. A simple shared drive and trusted connections along with BULK INSERT will do fine for what most people want or need to do.
On the subject of xp_CmdShell, if someone get's the heebie-geebies at the mere mention of xp_CmdShell or a trusted OPENQUERY that uses ACE drivers, then SSIS is definitely the way to go. Since we've been on that subject before, I'll remind folks that xp_CmdShell isn't a security risk. Bad security is a security risk because any hacker that get's into your system with "SA" privs is going to change your career by using xp_CmdShell whether you have it turned off or not.
--Jeff Moden
Change is inevitable... Change for the better is not.