SSIS FTP Sending Blank Files

Question

I'm supporting an existing data interface that is a series of SSIS packages (SQL 2008 R2) for sending and receiving data with a third party. The communication is being done through FTP, and it uses a SSIS Script Task to call the FtpClientConnection objects.

This has been working fine for years, and then a few days ago it suddenly started randoming zero-length files. It runs every 15 minutes, and if it's sending 10 files, 2 or 3 or 5 of them will be blank. The source files that it is trying to send on our side is good, and if we resend it through another FTP client (like FileZilla), they transmit fine.

Again, this just started happening at a specific time and has been happneing since then. No updates or installations were done on that server before then (the server has been rebooted after that, but the problem started before the reboot and it had not been rebooted for a week prior to that).

    Dim FtpServer As String = Dts.Variables("FtpServer").Value
    Dim FtpUser As String = Dts.Variables("FtpUser").Value
    Dim FtpPass As String = Dts.Variables("FtpPass").Value
    Dim FtpInbound As String = Dts.Variables("FtpInbound").Value
    Dim LocOutbound As String = Dts.Variables("LocOutbound").Value
    Dim FtpFile As String = Dts.Variables("FtpFile").Value
    Dim DistributorID As Integer = Dts.Variables("DistributorID").Value
    Dim UploadFile As String = LocOutbound & FtpFile

    Try
        Dim oCnMgr As ConnectionManager = Dts.Connections("FTP")
        Dim oFtp As FtpClientConnection = New FtpClientConnection(oCnMgr.AcquireConnection(Nothing))

        oFtp.ServerName = FtpServer
        oFtp.ServerUserName = FtpUser
        oFtp.ServerPassword = FtpPass
        oFtp.Retries = 5
        oFtp.UsePassiveMode = True

        If File.Exists(UploadFile) Then
            Dim aUp As New ArrayList
            aUp.Add(UploadFile)

            oFtp.Connect()
            oFtp.SetWorkingDirectory(FtpInbound)
            oFtp.SendFiles(aUp.ToArray(GetType(String)), FtpInbound, True, True)
            oFtp.Close()

            PostFtpFile(" UPDATE FtpUpload SET Uploaded=1 WHERE Filename='" & FtpFile & "' AND DistributorID=" & DistributorID.ToString & " ")
        End If
        ...

The FTP client connection is set up with a 60-second timeout, passive mode, and 5 retries. It was originally set to a chuck size of 1, but I also tried 10 and 100 chunk sizes with no improvement. The average file is is between 600 bytes and 2 KB.

Is there any additional way to troubleshoot this? The task thinks it's successful, but I can't find any way to get detailed diagnostics from the SSIS component. Is there a log file that gets generated anyhere, or a way to get back from the FTP component how many bytes it actually sent? Many other FTP component I've worked with outside of SSIS had options to record detailed logs of the transmission.

Any help or guidance would be appreciated. This system is scheduled to be rewritten in the next few months, but as of now it has brought my clients ecommerce business to a halt.

Thanks

Answer 1

May sound drastic but if you can't find a good way to debug within SSIS then you can always resort to Network Monitor or Wireshark. That way you can see what's happening during the FTP session. Another option may be to ask for some extra FTP logging on the receiving end.