Secure FTP Connection Manager
PreviousNext

Secure FTP Connection Manager can be used along with SFTP Task. This connection can be used to connect FTP Server using SFTP or FTP protocol.

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we are going to learn how to create Secure FTP connection. We are going to use local FTP Server, Here, you can also Download and Install Rebex Tiny SFTP Server for practice it.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. In order to connect Secure SFTP server in SSIS, you will need obtained FTP/SFTP Credential.
  3. Once you have your Credential you may proceed to create new SSIS package. In BIDS/SSDT create new SSIS package Project
  4. Right click on Connection Managers Panel to Create New SFTP Connection for Server, and Context Menu will appear, Select New Connection from the Context Menu.
    SFTP Task Operations - Connection
  5. Select ZS-SFTP Connection Manager from the Connection Managers list and Click on Add Button.
    SFTP Task Operations - Connection
  6. Now in Connection Manager UI, Select SFTP - SSH File Transfer Protocol and Enter your Host name and Select Logon Type Username and Password and Enter it.
    SFTP Task Operations - Connection
  7. Click on Test Connection. If test successful then hit OK to save the connection manager setting UI.
  8. Thats all, you will see Secure FTP Connection into Connection Managers.
    SSIS Secure SFTP Connection - Success

SFTP Connection - Encryption mode for FTP/SSL (i.e. FTPS), Explicit/Implicit Mode

SFTP Connection - Encryption mode for FTP/SSL (i.e. FTPS or FTP over SSL/TLS), Explicit/Implicit Mode

SFTP Connection - Proxy Settings

ssis-sftp-connection-manager-proxy-settings-socks-httpconnect

SFTP Connection - Account Name/Verify checksum option for Classic FTP

ssis-sftp-connection-ftp-account-verify-checksum

SFTP Connection - SSL/TLS version for FTPS (i.e. FTP over SSL/TLS)

ssis-sftp-connection-ftp-ftps-ssl-tls-version-ignore-certificate-error

SFTP Connection - Advanced settings

ssis-sftp-connection-ftp-ftps-ssl-tls-version-ignore-certificate-error

Properties

Property Name Description
RetainSameConnection When you set this to True it keeps connection open until package is running. By default connection is released when task using connection is done executing. This setting sometimes can increase performance specially when you looping many files.
Protocol Protocol for file transfer

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Ftp [0] FTP - File Transfer Protocol
Sftp [1] SFTP - SSH File Transfer Protocol
ServerName Remote server name
Port Remote server Port number. 0 mean default port for selected protocol settings. For SFTP default port=22, For FTPS implicit TLS/SSL default port=990. For classic FTP without encryption default port=21.
UserName Userid for FTP connection
Password Password for FTP connection
Domain Domain for ftp connection (only used with SFTP LogonType=Ntlm or Kerberos).
LogonType Authentication type for SFTP (for FTP or FTP/SSL its always Userid/Password authentication)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Username and Password
KeyFile [1] Public/private key authentication
Certificate [2] X509 certificate authentication (VanDyke VShell, Tectia Server)
KerberosSingleSignOn [3] KerberosV5 single sign-on (GSSAPI)
KerberosUserPasswordDomain [4] KerberosV5 user/password/domain (GSSAPI)
NtlmSingleSignOn [5] NTLM single sign-on (GSSAPI)
NtlmUserPasswordDomain [6] NTLM user/password/domain (GSSAPI)
EncryptionMode Encryption type for FTP over SSL (i.e. FTPS). This setting is not used when Protocol=SFTP.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] No encryption
TlsSslImplicit [1] TLS/SSL Implicit encryption
TlsSslExplicit [2] TLS/SSL Explicit encryption
PassPhrase Password to decrypt private key file or X509 Certificate file you specified for authentication
KeyFilePath Private key file for SSH key based authentication. Only used when Protocol=SFTP and LogonType=KeyFile. Specify PassPhrase to decrypt private-key file if it is encrypted with password
ClientCertificateFilePath Client X509 Certificate file to send for authentication. You can use this with SFTP or FTP if remote server supports Client Certificate based authentication. Specify PassPhrase to decrypt certificate file if it is encrypted with password
ProxyType Proxy server type

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] None
Socks4 [1] Socks4
Socks4a [2] Socks4a
Socks5 [3] Socks5
HttpConnect [4] HttpConnect
ProxyHost Proxy Server or IP (e.g. MyProxy01 --or-- 192.168.0.1 --or-- proxy1.mydomain.com)
ProxyPort Proxy server port number
ProxyAuthMode Proxy authentication mode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Basic [0] Basic
Ntlm [1] Ntlm
Digest [2] Digest
ProxyUser UserName for proxy authentication
ProxyPassword Password for proxy authentication
ProxyDomain Domain to submit to the proxy server for authentication.
Account FTP Account (Not very common but used along with UserID/Password by some server). This option not supported when Protocol=SFTP
VerifyChecksumAfterTransfer Verify checksum after file is transferred. Many modern FTP servers support verifying file integrity after transfer (but not all FTP server supports this feature). If you using FTP/SSL (i.e. FTPS) then don't use this option because it has built-in integrity checking (either using MD5 or SHA1) and should never transfer a damaged data packet.
IgnoreCertificateErrors Ignore any certificate related errors when using FTP with SSL/TLS (Encryption on)
MinSslTlsVersion Min version of SSL/TLS when using FTPS (i.e. FTP with SSL/TLS)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
SSL30 [1] SSL30
TLS10 [2] TLS10
TLS11 [4] TLS11
TLS12 [8] TLS12
TLS13 [16] TLS13
Any [65535] Any
DoNotReuseControlConnectionSession Do not re-use control connection session ID for data connections. Check this option if your connection is getting dropped randomly (Useful for certain servers e.g. vsftpd, tous)
DoNotReuseSslSessions Gets or sets a value indicating whether to reuse TLS/SSL sessions for data transfers. Check this option if your connection is getting dropped randomly (Useful for certain servers e.g. vsftpd, tous)
MaxSslTlsVersion Max version of SSL/TLS when using FTPS (i.e. FTP with SSL/TLS)

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
SSL30 [1] SSL30
TLS10 [2] TLS10
TLS11 [4] TLS11
TLS12 [8] TLS12
TLS13 [16] TLS13
Any [65535] Any
TransferMode Data transfer mode (e.g. Binary vs Text).

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Binary [0] Binary
Text [1] Text (Ascii)
TrustedFingerprints Fingerprints of SFTP Server or Thumbprints of X509 Certificate you want to trust. Fingerprint/Thumbprint. Leave blank to skip validation. Use comma (,) to separate multiple fingerprints/thumbprints. This check authenticates remote server / certificate by its internal ID entered here.
MaxDownloadSpeedKbps Maximum download Speed in Kbps
MaxUploadSpeedKbps Maximum upload Speed in Kbps
UseActiveMode Use ActiveMode rather PassiveMode
EnableCompression Enable ZLib Compression
EnableRestoreDateTime Restore LastWriteTime from source file to target file after transfer is completed
DateTimeItemsForRestore DataTime Properties you like to restore after transfer

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
CreationTime [1] CreationTime
LastWriteTime [2] LastWriteTime
LastAccessTime [4] LastAccessTime
All [7] All
DefaultDirectory Default directory path after logon. When you set this option it will issue CWD {user-supplied-path}. This option is helpful when your server is changing default directory after logon to other than ROOT (i.e. / )
TreatUnknownItemsAsFiles Turn on this option if you get '{item} is not a file or directory' error. This workaround for servers which don't provide itemtype when you get item listing.
DisableSftp4 Disable SFTP v4 protocol. This is workaround for error 'Operation not supported'
DisableTransferQueue Disable item queuing. Turn on this option when target server doesn't handle queuing correctly in some cases.
UseSmallPackets Use small packets (4KB) and avoid splitting them.
WaitForServerWelcomeMessage Wait for server SSH welcome message before sending any client message. Workaround for servers which throws 'Timeout exceeded while waiting for welcome message' due to this behavior.
MinimumKeySize Specifies minimum Key Size (RSA or DiffieHellman). Key weaker than specified bits in this property is rejected. 0=Default setting (i.e. 1024 bits)
PreferredHostKeyAlgorithm Specifies which host key Algorithm to use when server supports multiple Algorithms (e.g. RSA, DSS). Enter [Any] to enable all including. Leave blank to use default. Available options are RSA, DSS, Certificate, ED25519, ECDsaNistP256, ECDsaNistP384, ECDsaNistP521, None or Any
KeyExchangeAlgorithms Specifies list of KeyExchangeAlgorithms which are allowed. Enter [any] to enable all including unsecure ciphers.
HostKeyAlgorithms Specifies list of HostKeyAlgorithms which are allowed. Enter [any] to enable all including unsecure ciphers.
MacAlgorithms Specifies list of MacAlgorithms which are allowed. Enter [any] to enable all including unsecure ciphers.
EncryptionAlgorithms Specifies list of EncryptionAlgorithms which are allowed. Enter [any] to enable all including unsecure ciphers.
KeepAliveDuringTransfer Send NOOP command every 10 minutes. In rare case this setting is needed to keep connection alive (Specially when you have buggy router/firewall).
PauseBeforeUploadClose Add small delay before encrypted connection is closed. Use this workaround for some servers which cant handle last block of data correctly during secure connection.
SslDoNotInsertEmptyFragment Do not send empty record after successful handshake with CBC ciphers.
UseLargeBuffer Use large buffer to improve performance.
LegacyGroupExchangeMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
Legacy [1] Legacy
Standard [2] Standard
DoNotSendSignals The default behavior of FTP/SSL is to send IP and Synch signals before aborting the download using the ABOR command, unless a proxy is in use. Setting this flag will disable sending of IP and Synch signals even through non-proxied FTP sessions. You might want to try disabling it if you experience problems while aborting data transfers.
DoNotSendAbort Do not send ABOR command while download process is still aborting.
Timeout Operation timeout in seconds. Timer resets after response is received from server
OtherOptions Other less common options for SFTP / FTP modes which are not exposed via UI. You can set one or more options using comma (e.g. Option1,Option2). Available options are as below:
=========================================================
===========  SFTP Options (Secure FTP) ==================
=========================================================
DisableTransferQueue ………… Disable queuing of read and write requests.
DownloadBufferSize ………… Sets the size of data packets used during download. Supported sizes are 4 KB to 48 KB.
DownloadQueueLength ………… Sets the length of packed queue for downloads.
EnableBrokenRealPathWorkaround ………… Gets or sets a value that enables workaround for broken SSH_FXP_REALPATH command used to determine a canonical path when changing or creating a directory.
EnableSignaturePadding ………… Enable padding of RSA private key signatures (workaround for SSH servers that got the padding wrong).
EnsureKeyAcceptable ………… Announce public key to the server before performing key authentication.
LogChecksums ………… Gets or sets a value that makes the client calculate checksums of files being uploaded and logs them into the log.
MultiFileLinkMode (Use below options) ………… Gets or sets the default processing mode of detected links in multi-file operations.
    MultiFileLinkMode_SkipLinks
    MultiFileLinkMode_FollowLinks
    MultiFileLinkMode_ThrowExceptionOnLinks
MultiFileMoveMode (Use below options) ………… Gets or sets the default move behavior in multi-file transfers.
    MultiFileMoveMode_All
    MultiFileMoveMode_FilesOnly
PostponeChannelClose ………… Postpone channel close requests (workaround for older versions of ssh.com and Cisco SSH servers).
RaiseEventsFromCurrentThread ………… Specifies whether to raise asynchronous events from the current working thread or whether to raise them using a SynchronizationContext available when an asynchronous operation was started.
RecheckItemExistence ………… In multi-file operations it forces checking, whether currently processing item still exists.
RestoreDateTime ………… Gets or sets a value that indicates which date/time is restored after successful transfer if possible.
RevertSymlinkArguments ………… Gets or sets a value that indicates whether to force the reversal of SSH_FXP_SYMLINK arguments.
SkipDuplicateItems ………… Set this to throw error if duplicate file names detected. By default duplicate name is ignored and only one name is returned.
TimeComparisonGranularity ………… Gets or sets a value which specifies granularity for time comparisons.
    TimeComparisonGranularity_Days
    TimeComparisonGranularity_Seconds
    TimeComparisonGranularity_TwoSeconds
TreatUnknownItemsAsFiles ………… Gets or sets a value specifying that filesystem items with unknown type (not file nor directory nor symlink) will be processed as files.
TryPasswordFirst ………… When authenticating using both password and private key, try password authentication first (default is to try the private key first).
UseLargeBuffers ………… Use larger receive buffer for incoming and outgoing data.
UsePosixRename ………… Gets or sets a value that indicates whether to use POSIX rename instead of standard SFTP rename (when available).
UseReadWriteModeForDownloads ………… Opens file for downloading (in GetFile(String, String)/Download(String, String, TraversalMode) methods) in read-write mode instead of read-only mode.
UseSmallPackets ………… Use small data packets (4KB each) during upload and never split them.
UseFipsAlgorithmsOnly ………… Enables FIPS Only algorithms.
DoNotUseFipsAlgorithmsOnly ………… Disables FIPS Only algorithms (Use this to override Windows FIPS setting and ignore Windows FIPS mode).
WaitForServerWelcomeMessage ………… Wait for server SSH protocol welcome message before sending the client one.

=========================================================
===========  FTP Options (Classic FTP / FTPS) ===========
=========================================================

AlwaysResolveServerName ………… Always resolves the server hostname by performing a DNS query. This means a DNS query will be done when using any proxy as well.
ConnectPassiveLater ………… Makes the passive mode transfer initialize the connection only after the LIST/NLST/RETR/STOR command has been sent.
DisableImplicitAs400NameFormat ………… Gets or sets a value indicating whether to automatically issue `SITE NAMEFMT 1` command when AS/400 system is detected in a batch operation such as Download or Upload. Default is false, which means the `SITE NAMEFMT 1` is issued.
DisableInvalidPassiveModeAddressWorkaround ………… Disables a workaround for FTP servers that announce private range IP addresses even though a public IP was used to connect to them. By default, when such server is detected, an IP address announced by the server in response to PASV command used is overrode with an IP used by the control connection.
DisableProgressPercentage ………… Disables progress percentage reporting through TransferProgressChanged event.
DoNotDetectFeatures ………… Disables feature detection using FEAT and other commands.
DoNotSendAbort ………… Do not send the ABOR command when aborting download.
DoNotSendSignals ………… Do not send abort signals when aborting download.
EnableControlConnectionFlushing ………… Enables reading and throwing out any unexpected incoming responses when sending an FTP command.
EnableDummyRenameForFileExists ………… Gets or sets a value that indicates whether to use RNFR/RNTO commands to determine remote file existence. Only supported for Windows CE FTP server.
EnableFactsNegotiation ………… Gets or sets a value indicating whether to attempt to negotiate extended MLST/MLSD facts using OPTS MLST command.
EnableImproperDataCloseWorkaround ………… Some firewalls and some versions of ActiveSync were observed not to close data connections properly, causing '426' server errors. This option enables a workaround for this.
EnableMultipleBlockingCalls ………… Makes it possible to call multiple methods from different threads at once. The calls will block until the operation is completed.
ForceActiveCcc ………… Forces the CCC command behavior that was used by older FTP/SSL releases.
ForceExtendedDataChannelCommand ………… Gets or sets a value indicating whether to use EPSV/EPRT commands to initiate a data channel.
ForceListHiddenFiles ………… Use [LIST -la] instead of [LIST] in List operation.
ForceMdtmForGetFileDateTime ………… Forces MDTM command to be always used by GetFileDateTime(String) instead of MLST (which is used when available by default).
ForceSilentCcc ………… This option is deprecated and should no longer be used.
ForceSimpleDirectoryExists ………… Specifies whether to just use a combination of PWD and CWD commands when determining remote directory existence.
IgnorePassiveModeAddress ………… Overrides an IP address announced by the server in response to PASV command with an IP used by the control connection.
KeepAliveDuringTransfer ………… Send NOOP command during file transfers every 10 minutes.
MultiFileLinkMode (Use below options) ………… Gets or sets the default processing mode of detected links in multi-file operations.
    MultiFileLinkMode_SkipLinks
    MultiFileLinkMode_FollowLinks
    MultiFileLinkMode_ThrowExceptionOnLinks
MultiFileMoveMode (Use below options) ………… Gets or sets the default move behavior in multi-file transfers.
    MultiFileMoveMode_All
    MultiFileMoveMode_FilesOnly
PauseBeforeUploadClose ………… Performs a small pause before closing the encrypted upload connection when the transfer is finished. Used as a workaround for servers that are unable to correctly handle the last block data if it is followed by a TLS CloseNotify message in the same TCP block.
RaiseEventsFromCurrentThread ………… Specifies whether to raise asynchronous events from the current working thread or whether to raise them using a SynchronizationContext available when an asynchronous operation was started.
RecheckItemExistence ………… In multi-file operations it forces checking, whether currently processing item still exists.
ReuseControlConnectionSession ………… Re-use control connection session ID for data connections.
SendDataAsynchronously ………… Deprecated. Doesn't have any effect now.
SkipDuplicateItems ………… Gets or sets a value that indicates whether to skip duplicate items in directory listings or whether to fail.
SslAcceptAllCertificates ………… Specifies whether to accept all server certificates by default. This is strongly discouraged in production environment.
UseLargeBuffers ………… Use larger receive buffer for incoming and outgoing data.
UseLegacyPaths ………… Specifies whether to use old API style paths (relative) or new API style paths (absolute) in multi-file transfers.
UseFipsAlgorithmsOnly ………… Enables FIPS Only algorithms.
DoNotUseFipsAlgorithmsOnly ………… Disables FIPS Only algorithms (Use this to override Windows FIPS setting and ignore Windows FIPS mode).
UploadCommand_PUT ………… Use PUT as UploadCommand rather than sending STOR to FTP server. This may be needed in rare case when FTP server doesn’t accept STOR command
DisableMachineProcessingList ………… Some FTP server cannot handle MLST (By default its ON). In such case set this option to disable MLST extension (MLST and MLSD commands) - directory and file listings for machine processing.
ConnectionString ConnectionString for this connection manager. If you don't want to set each property separately then just adjust full connectionstring (e.g. prop1Name=val;prop2Name=val) to configure entire connection manager

Setting UI

SSIS Secure FTP Connection - Setting UI

See Also

References


Copyrights reserved. ZappySys LLC.