SSIS: Transfer Database Task

Goal: Copy a database from a machine I don’t control to one I do using Transfer Database Task.

Web hosting scenario: If you don’t have sysadmin rights on the source machine, you can’t use offline transfering, which appears to use a detach-copy-reattach process.

On the otherhand, online tranfer wont work either, since you need a network share to both servers!

Patching: Ideally, you are at service pack 2 or later and probably even more ideally, the source and destination are at the same service pack level.  I ran into an issue where I couldn’t copy from SQL2005RTM to SQL2005SP2 on account of SMO versioning problems.

Advice: The Transfer Database Task is really only good if you have full control over both source and target machines and both are on the same network.

Rebuttal: ”Yeah, but SSIS is supposed to be a corporate tool, not a tool for people who don’t own both machines”  And as far as I can tell, SSMS uses the SSIS Transfer Database Tasks as it’s database copying mechanism.