2

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

  • 1
    The SendFiles method doesn't have a return type. If it encountered issues transmitting, it'd blow up in spectacular fashion. Is it possible that your Catch block (unshown) is swallowing the error message? – billinkc Dec 2 '13 at 21:17
  • No, SendFiles completes successfully. The next line inside the Try block writes back to the DB that it was successful, and that line is getting executed. – Mike Mooney Dec 2 '13 at 21:27
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.