SSIS FTP file name and file sizes list

Question

We use the FTP task in SSIS to download about 500 files from an FTP server, with the biggest file about 2 Gigabytes in size. The download fails 50% of the time. We would like to implement a step in our SSIS package that would retrieve the list of files on the FTP Server and the file sizes before we try download them for processing. The FTP Task doesn't have an operation that retrieves the file list and the file sizes. Can you provide an example of how to do this?

Below is the Script Task code block I've found to retrieve the file name list and store in an XML file.

    Dim ftpFileNameListXML As New StringBuilder
    ftpFileNameListXML.AppendLine("<filelist>")

    Dim ftpcm As ConnectionManager = Dts.Connections("FTP")
    Dim ftp As FtpClientConnection = _
                New FtpClientConnection(ftpcm.AcquireConnection(Nothing))
    Dim ftpFileNames() As String
    Dim ftpFolderNames() As String

    ftp.Connect()
    ftp.SetWorkingDirectory(Dts.Variables("FtpWorkingDirectory").Value.ToString())
    ftp.GetListing(ftpFolderNames, ftpFileNames)
    ftp.Close()

    Dim i As Integer
    For i = 0 To ftpFileNames.GetUpperBound(0)
        ftpFileNameListXML.Append("<file name='")
        ftpFileNameListXML.Append(ftpFileNames(i))
        ftpFileNameListXML.AppendLine("'/>")
    Next i

    ftpFileNameListXML.AppendLine("</filelist>")
    Dts.Variables("FtpFileListXML").Value = ftpFileNameListXML.ToString()

    Dts.TaskResult = ScriptResults.Success

http://stackoverflow.com/questions/6698448/need-to-get-filesize-via-ssis-package-task — J.S. Orris, Apr 16 '15 at 20:28
I'm not getting any errors, I'm struggling to find a way to retrieve the file size. — John W. Mnisi, Apr 16 '15 at 20:54
I've looked at the example already but it doesn't solve my issues. I want to retrieve the ftp file names and sizes, which I'll use for validation at a later stage. The files will be downloaded at a later stage after I've retrieved the file sizes. — John W. Mnisi, Apr 16 '15 at 21:10
Is your script not working? Or do you not know how to implement it in SSIS? — J.S. Orris, Apr 16 '15 at 21:12
The script works correctly, and I can list all filenames on the FTP server. I'm struggling to find a method / way to get the file sizes. — John W. Mnisi, Apr 16 '15 at 21:15

J.S. Orris · Answer 1 · 2015-04-16T21:28:19.670

1

Here is a link to get the file sizes in VB: http://www.codeproject.com/Questions/205915/VB-NET-get-the-size-of-a-file-Downloading-in-FTP-S

"You should use the GetFileSize[^] field to know in advance the selected file size."

Dim ftp As Net.FtpWebRequest = GetRequest(URI) ftp.Method = Net.WebRequestMethods.Ftp.GetFileSize

Her is another link http://www.dreamincode.net/forums/topic/317225-want-to-get-ftp-server-file-name-fil-size-in-listview/

Try using ListDirectoryDetails

edited Apr 16 '15 at 21:28

answered Apr 16 '15 at 21:13

J.S. Orris

4,653
12
49
89

Yes the script works correctly, and I can list all filenames on the FTP server. I'm struggling to find a method / way to get the file sizes. How do I get the file sizes? – John W. Mnisi Apr 16 '15 at 21:16
did you add a `Script Task` to your control flow? – J.S. Orris Apr 16 '15 at 21:18
Yes, the above code runs successfully on a Script Task and I can list all file names on the FTP server. – John W. Mnisi Apr 16 '15 at 21:21
I found this link to get file size: http://www.codeproject.com/Questions/205915/VB-NET-get-the-size-of-a-file-Downloading-in-FTP-S Sorry about the confusion "You should use the GetFileSize[^] field to know in advance the selected file size." – J.S. Orris Apr 16 '15 at 21:22

SSIS FTP file name and file sizes list

1 Answers1