guide_ssis » Revisions »
Differences
This shows you the differences between the selected revisions of the page.
| guide_ssis 2014-03-10 | guide_ssis 2022-08-12 (current) | ||
| Line 1: | Line 1: | ||
| - | ====== SFTP Task for SSIS ====== | + | ====== SFTP Task for SSIS/SSDT ====== |
| - | //This guide contains description of creating SFTP((SFTP is also incorrectly known as Secure FTP.)) file transfer task for SSIS using WinSCP [[scripting]]. **Preferred approach is to use [[library_ssis|WinSCP .NET assembly from SSIS script task .NET code]].**// | + | //This guide contains description of creating SFTP((SFTP is also incorrectly known as Secure FTP.)) file transfer task for SSIS using WinSCP [[scripting]]. **The preferred approach is to use the [[library_ssis|WinSCP .NET assembly from SSIS script task .NET code]].**// |
| WinSCP offers [[scripting]] interface that you can use to automate file transfers to/from SFTP server. | WinSCP offers [[scripting]] interface that you can use to automate file transfers to/from SFTP server. | ||
| + | |||
| + | ===== Before Starting ===== | ||
| Before starting you should: | Before starting you should: | ||
| Line 11: | Line 13: | ||
| Start by creating WinSCP script file to transfer your files. | Start by creating WinSCP script file to transfer your files. | ||
| - | Below you can see basic script example for downloading file from SFTP server. For details see [[guide_automation|guide to automation]] or [[scripting|detailed documentation of scripting functionality]]. | + | Below you can see a basic script example for downloading file from SFTP server. For details see [[guide_automation|guide to automation]] or [[scripting|detailed documentation of scripting functionality]]. You can also have WinSCP [[guide_automation#generating|generate a script template]] for you. |
| - | <code> | + | <code winscp> |
| - | option batch abort | + | open mysession -hostkey="ssh-rsa 2048 xxxxxxxxxxx..." |
| - | option confirm off | + | |
| - | open mysession -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx" | + | |
| get /remotepath/data.dat c:\localpath\ | get /remotepath/data.dat c:\localpath\ | ||
| exit | exit | ||
| Line 23: | Line 23: | ||
| In the script you need at least to: | In the script you need at least to: | ||
| * Replace ''mysession'' argument to ''[[scriptcommand_open|open]]'' command with specification of SFTP connection in form ''%%sftp://username:password@hostname/%%'' or use name of [[session_configuration#site|site]]. | * Replace ''mysession'' argument to ''[[scriptcommand_open|open]]'' command with specification of SFTP connection in form ''%%sftp://username:password@hostname/%%'' or use name of [[session_configuration#site|site]]. | ||
| - | * Replace host key fingerprint after ''-hostkey'' switch of ''[[scriptcommand_open|open]]'' command with actual fingerprint of your [[ssh#verifying_the_host_key|SFTP/SSH server host key]];((Note that depending on encryption method, the host key may have different format than shown in the example)) | + | * Replace host key fingerprint after ''[[scriptcommand_open#hostkey|-hostkey]]'' switch of ''[[scriptcommand_open|open]]'' command with actual fingerprint of your [[ssh_verifying_the_host_key|SFTP/SSH server host key]];((Note that depending on encryption method, the host key may have different format than shown in the example.)) |
| * Replace paths after ''[[scriptcommand_get|get]]'' command with actual paths to remote file to download from and local directory to download to. | * Replace paths after ''[[scriptcommand_get|get]]'' command with actual paths to remote file to download from and local directory to download to. | ||
| Save the script into file accessible from SSIS. | Save the script into file accessible from SSIS. | ||
| - | ===== SSIS task ===== | + | ===== [[task]] SSIS/SSDT task ===== |
| - | In SSIS, add new //Execute Process// task to control flow of your package. In the //Execute Process Task Editor//: | + | In SSIS/SSDT, add new //Execute Process Task// to control flow of your package. In the //Execute Process Task Editor//: |
| - | * Put path to WinSCP [[executables|executable]] (by default ''C:\Program Files\WinSCP\WinSCP.exe'') into //Executable//; | + | * Put path to WinSCP [[executables|executable]] (by default ''C:\Program Files (x86)\WinSCP\WinSCP.exe'') &winpath &win64 into //Executable//; |
| - | * In //Arguments// specify ''/script=<pathtoyourscript>''; | + | * In //Arguments// specify ''[[commandline#scripting|/script=<pathtoyourscript>]]'' (Alternatively you can specify all commands here using ''[[commandline#scripting|/command]]'' switch, as used [[ui_generateurl|the command-line template]] that WinSCP can generate for you); |
| * You may want to set //WorkingDirectory// to path, where you want to download files to/upload files from, if you do not use absolute paths in your script. | * You may want to set //WorkingDirectory// to path, where you want to download files to/upload files from, if you do not use absolute paths in your script. | ||
| Line 41: | Line 41: | ||
| * [[commandline|Command-line]] parameters; | * [[commandline|Command-line]] parameters; | ||
| * Guide to [[guide_automation|scripting]]; | * Guide to [[guide_automation|scripting]]; | ||
| - | * [[faq#scripting_automation|FAQs about scripting]]; | + | * [[faq#scripting|FAQs about scripting]]; |
| * [[troubleshooting|Troubleshooting]]. | * [[troubleshooting|Troubleshooting]]. | ||
| ===== Other References ===== | ===== Other References ===== | ||
| - | * [[http://www.codeproject.com/KB/database/SSIS_SFTP.aspx|SFTP with SSIS Packages]]. | + | * [[https://www.codeproject.com/Articles/23740/SFTP-with-SSIS-Packages|SFTP with SSIS Packages]]. |