Is it Possible to Backup and Transfer files through T-SQL using SSIS

Question

Here is a situation,

There is SERVER A and SERVER B, SERVER B wants to restore backup taken by SERVER A on its own local machine now I know I have a little bigger way to setup FTP etc to transfer the FILE but I want to know that can I write a SSIS Package which would do file transfer from SERVER A to SERVER B without depending upon FTP/HTTP etc.

Please limit your answer only centered to Sql Integration Services, I understand Replication/WebSync/Mirroring but the problem here is absolutely different.

For an example, XP_CMDSHELL offers command shell integration with SQL, and you can use it in SQL to do some commands, my question is can I write something like XP_CMDSHELL which will do my job whatever it may be, I can use c# to do anything I want in it. And that can be offered from remote, like I can write open a file and read its contents from other machine, I know about security but I will manage security there.

Answer 1

Your question is hard to answer because you have said no FTP: there are only a few practical ways to transfer files from one machine to another remotely.. and FTP is probably the most popular.

However, SSIS gives you the Transfer database task. I haven't used it myself though so can't say if it meets your needs.

Other SSIS options would be FTP Task or File System tasks which is relies on standard FTP or something like xcopy underneath. xp_cmdshell would do exactly the same: FTP or xcopy.

Perhaps more information on your environment might help