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.