SQL Server

AWS Transfer for SFTP (Amazon S3)

See more SFTP Examples

Once you've setup your AWS Transfer for SFTP in the AWS Console, interacting with it is no different than any other SSH/SFTP server. AWS will provide a private key in PEM format. It is used for authentication (instead of a password). SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @sftp int
    EXEC @hr = sp_OACreate 'Chilkat.SFtp', @sftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to the AWS SFTP server.
    -- Change the domain to your value.
    DECLARE @domain nvarchar(4000)
    SELECT @domain = 's-123456df999999fab.server.transfer.eu-west-2.amazonaws.com'
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @sftp, 'Connect', @success OUT, @domain, @port
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Load your AWS SFTP private key PEM file..
    DECLARE @sshKey int
    EXEC @hr = sp_OACreate 'Chilkat.SshKey', @sshKey OUT

    DECLARE @keyText nvarchar(4000)
    EXEC sp_OAMethod @sshKey, 'LoadText', @keyText OUT, 'qa_data/pem/s3_sftp_privateKey.pem'
    EXEC sp_OAGetProperty @sshKey, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @sshKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END
    EXEC sp_OAMethod @sshKey, 'FromOpenSshPrivateKey', @success OUT, @keyText
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sshKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END

    -- Authenticate with the SSH server using the private key.
    EXEC sp_OAMethod @sftp, 'AuthenticatePk', @success OUT, 'myUsername', @sshKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END

    -- After authenticating, the SFTP subsystem must be initialized:
    EXEC sp_OAMethod @sftp, 'InitializeSftp', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END

    -- Upload from the local file to the SSH server.
    -- Important -- the remote filepath is the 1st argument,
    -- the local filepath is the 2nd argument;
    DECLARE @remoteFilePath nvarchar(4000)
    SELECT @remoteFilePath = 'hamlet.xml'
    DECLARE @localFilePath nvarchar(4000)
    SELECT @localFilePath = 'c:/temp/hamlet.xml'

    EXEC sp_OAMethod @sftp, 'UploadFileByName', @success OUT, @remoteFilePath, @localFilePath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @sftp
    EXEC @hr = sp_OADestroy @sshKey


END
GO