July 15, 2014 at 2:57 pm
We have SQL Server 2012 running on Windows 2008 Server. We would like to use a SSIS Package to generate a text file and then secure copy it to a vendor's ftp site. Would it be best to use an FTP Task or Execute Process Task (to call the batch file)? Would I need to install some software like winscp or does the Windows O/S has some secure copy or ftp programs that may be used? Please provide a simple example.
Thanks, Kevin
July 15, 2014 at 5:25 pm
kevinsql7 (7/15/2014)
We have SQL Server 2012 running on Windows 2008 Server. We would like to use a SSIS Package to generate a text file and then secure copy it to a vendor's ftp site. Would it be best to use an FTP Task or Execute Process Task (to call the batch file)? Would I need to install some software like winscp or does the Windows O/S has some secure copy or ftp programs that may be used? Please provide a simple example.Thanks, Kevin
I apologize as I haven't worked with SSIS/2012 yet, but here is an example using powershell that I use. You can call it from within SSIS.
PSCP Example
pscp.exe -l $FtpUserName -pw $FtpPassword -sftp $LocalFile_Ack_FullPath $FtpHost":"$RemoteFolder$RemoteFile_Ack | Out-Null
Please see http://www.chiark.greenend.org.uk/~sgtatham/putty/docs.html for more pscp syntax.
WinSCP Example
This is just a download example, you this should get you started.
&$FTPProgramPath\winscp.com /log=$FTPProgramPath\winscp.log /command `
"option batch abort" `
"option confirm off" `
"open $FtpUserName"":""$FtpPassword""@""$FtpHostName" `
"cd $FtpFolder" `
"get $CurrentDownloadFile $LocalFolder\$CurrentDownloadFile" `
"bye"
Please see http://winscp.net/forum/index.php for more winscp syntax.
Set up all the variables before hand and pass them to the executable. If you can, put the FTP executables in a path that can be executed anywhere, otherwise, you have to make sure to supply the FQPN.
Additionally make sure you log onto the server as the account that will be executing the ftp processes and connect to the FTP server at least once to save the SSH key to the registry.
July 17, 2014 at 2:25 pm
Thanks for the response Steve T. I am trying to use WinSCP and follow the example below (which I got from the website you provided: http://winscp.net/eng/docs/scripting) It appears the below example is only using the login and not a password. This is what I want.
To your statement "Additionally make sure you log onto the server as the account that will be executing the ftp processes and connect to the FTP server at least once to save the SSH key to the registry." Do I need the hostkey of the ftp destination server in order to connect? If so, I may have to contact the vendor to see if they will provide me with their hostkey?
I have set up "SCP with no password" on unix servers and I had to generate private/public keys and send the public key to the vendor for them to put on their ftp server. From a windows machine, I am not sure if I just need to get the hostkey from the vendor or generate private/public keys. If this is required, how do you generate private/public keys on a windows machine?
Below is the example I am trying to follow:
-------------------------------------------------------------------
In the example below, WinSCP connects to example.com server with account user, downloads file and closes the session. Then it connects to the same server with the account user2 and uploads the file back.
# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
# Connect using a password
# open sftp://user:password@example.com/ -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
# Connect
open sftp://user@example.com/ -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
# Change remote directory
cd /home/user
# Force binary mode transfer
option transfer binary
# Download file to the local directory d:get examplefile.txt d:# Disconnect
close
# Connect as a different user
open sftp://user2@example.com/
# Change the remote directory
cd /home/user2
# Upload the file to current working directory
put d:\examplefile.txt
# Disconnect
close
# Exit WinSCP
exit
July 17, 2014 at 3:19 pm
kevinsql7 (7/17/2014)
To your statement "Additionally make sure you log onto the server as the account that will be executing the ftp processes and connect to the FTP server at least once to save the SSH key to the registry." Do I need the hostkey of the ftp destination server in order to connect? If so, I may have to contact the vendor to see if they will provide me with their hostkey?
Sorry, I misspoke. I meant the server's RSA key fingerprint and not SSH key.
When you initial connect to an ftp server, it will prompt you with the following warning:
The server's host key was not found in the cache. You have no guarantee that the server is the computer you think it is.
The server's rsa2 key fingerprint is:
ssh-rsa 2048 a0:dc:40:1f:a3:53:00:73:27:0c:45:ae:8f:ee:03:ef (This is finger print for http://ftp.entsupport.symantec.com)
If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
Continue connecting and add host key to the cache.
See http://winscp.net/eng/docs/faq_script_hostkey for more info.
It appears the below example is only using the login and not a password. This is what I want.
I'm confused, why would you connect to a secure ftp site and not have a password? Assuming the account is set up with a blank password, you would pass a "" as the password.
In powershell, it would be [string]$Password = ""
I have set up "SCP with no password" on unix servers and I had to generate private/public keys and send the public key to the vendor for them to put on their ftp server. From a windows machine, I am not sure if I just need to get the hostkey from the vendor or generate private/public keys. If this is required, how do you generate private/public keys on a windows machine?
I think you're talking about PGP now which I'm not super familiar with. You may need to ask about this on a different forum. I also use Robo-Ftp which has it's own PGP module. See http://www.robo-ftp.com/technical-support/getting-started/pgp-intro/. Hopefully this will help you.
July 18, 2014 at 7:41 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply