The responses to my previous question suggested SSIS as the most efficient and reliable way to get files from AIX into SQL Server. I agree that it's probably a good idea. But for the sake of understanding the other options better, I'd like to narrow the scope a little bit.
Irrespective of what I plan to do with the files, how can I determine the most efficient and reliable method of retrieving from a Windows batch job files that are created by an AIX batch job, assuming I'm in control of both ends. The basic options I know of are sftp, scp and samba. I'm open to other options.
My criteria are as follows.
- Reliability. From my experience (maybe just a problem in our systems) ftp jobs in batch are among the most frequent to fail.
- Security. Both servers are on the internal network, but the transfer still needs to be secure.
- Maintainability. We have a fairly strict password expiration policy. So I'd like to minimize the number of different accounts and passwords.
- Efficiency. The amount of data to be transferred in this specific case is small but not trivial. I'd be interested to know whether any of these approaches performs and scales better than the others.
Any suggestions or input will be appreciated.