Download and Delete Matching Files

SQL Server example showing how to FTP download files matching a wildcarded file pattern and delete them from the FTP server.

Download Chilkat FTP2 ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @ftp int
    EXEC @hr = sp_OACreate 'Chilkat.Ftp2', @ftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  Any string unlocks the component for the 1st 30-days.
    EXEC sp_OAMethod @ftp, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    EXEC sp_OASetProperty @ftp, 'Hostname', 'www.example-code.com'
    EXEC sp_OASetProperty @ftp, 'Username', '****'
    EXEC sp_OASetProperty @ftp, 'Password', '****'

    --  Connect and login to the FTP server.
    EXEC sp_OAMethod @ftp, 'Connect', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Note: It may be tempting to call MGetFiles(pattern,localDir)
    --  followed by a call to DeleteMatching(pattern), however,
    --  if new files are uploaded to the FTP server while MGetFiles
    --  is running, those files will be deleted by DeleteMatching.

    --  Instead, it's best to set the ListPattern and then iterate
    --  over each file...

    --  Set the ListPattern = "*.xml" to match all XML files.
    EXEC sp_OASetProperty @ftp, 'ListPattern', '*.xml'

    --  NumFilesAndDirs contains the number of files and sub-directories
    --  matching the ListPattern in the current remote directory.
    --  The first time it is accessed after changing ListPattern,
    --  or after calling ChangeRemoteDir, a snapshot of the directory
    --  listing is fetched and saved in memory. During this loop,
    --  the value of NumFilesAndDirs will not change.
    DECLARE @i int

    DECLARE @fname nvarchar(4000)

    DECLARE @n int

    EXEC sp_OAGetProperty @ftp, 'NumFilesAndDirs', @n OUT

    IF @n < 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    IF @n > 0
      BEGIN
        SELECT @i = 0
        WHILE @i <= @n - 1
          BEGIN

            EXEC sp_OAMethod @ftp, 'GetFilename', @fname OUT, @i

            PRINT @fname

            --  Download the file into the current working directory.
            EXEC sp_OAMethod @ftp, 'GetFile', @success OUT, @fname, @fname
            IF @success <> 1
              BEGIN
                EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                RETURN
              END

            --  Now delete the file.
            EXEC sp_OAMethod @ftp, 'DeleteRemoteFile', @success OUT, @fname
            IF @success <> 1
              BEGIN
                EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                RETURN
              END

            SELECT @i = @i + 1
          END
      END

    EXEC sp_OAMethod @ftp, 'Disconnect', NULL
END
GO