Menu
I have a requirement to create a simple database in Access to collect some user data that will be loaded into another database for further reporting. There will be a module in the Access db that when invoked by the user (probably by clicking a button) will output a query to a delimited file. The user also needs a mechanism (for example a form with a button) to easily transfer the file to a remote server, using sftp. Does anyone have an idea of how to accomplish this?
608
You need to create an automated process to transfer files using Secure File Transfer Protocol (SFTP) with SQL Server Agent . Check out this article to learn how. These are the versions used for our examples:
Open a command prompt and call sftptest.bat If we execute the batch file again, we won't be prompted for the key. The point is to automate this so let's create a SQL Server Agent Job to call it. New Job… Chose Operating system (CmdExec) in the Type dropdown Enter the full path to C:\SftpTesting\SftpScripts\sftptest.bat Click OK twice.
A work around for this will be to use PSFTP through an Execute Process Task in SSIS to download the file to our local machine. PSFTP is a SFTP client tool provided by PuTTy to transfer files between computer systems using the SFTP protocol.
Useful Functionality in WinSCP. Now that stepped through manually creating an automated SFTP file transfer let's look at some very useful functionality in WinSCP. Run WinSCP.exe to open the GUI client. Select SFTP in File Protocol dropdown. Enter Host name. Port number. Enter User name. Enter Password. Click Login.
You can simply write a call to the sftp command line client via a batch file if you want to accomplish that.
Check out the Shell() function in VBA.
Under the click event of the button on your form add in the code:
mySFTPCall = "sftp <insert your options here!>"
Call Shell(mySFTPCall, 1)
I've used this before to just copy files straight across network shares etc. to share data from an in-house Access DB. Of course you could get more fancy if necessary.
80
You can use our SFTPBlackbox (ActiveX Edition).
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
