Question: I have an ETL process which I'm looking to streamline. The rewrite is occurring through Powershell scripting. How can I copy a file on a remote share to a remote server?
Answer: You can manage this copy using a Powershell cmdlet called Copy-Item . At a basic level it is easy to implement this script here is an example of a copy from a remote share to a remote share on another server
Copy-Item \\server1\test\file.txt \\server2\test_copy\test
If this Copy-Item is part of an ETL process, you'll need to think about how you will manage a failure and maybe consider if you require a buffered or non-buffered approach to file transfers.
If you are copying very large files across the network - then speed and dependability are important. As an alternative to the Copy-Item approach or MS-DOS COPY , you can consider an Speed up copying large files with ESEUTIL - for large file transfer
Which ever approach you decide upon - manage the ETL through SQL Server agent jobs and error handling .
Find more Powershell Scripts