0

Using the script task below to stuff list of files into User::ftp_file_list. But, the For Each Loop container in the next step returns the error "Foreach Loop Container: The object in the variable "User::ftp_file_list" does not contain an enumerator."

OK, turns out that the issue is that the connection is not being made. The thing I don't understand is that I've tested the connection independently without any trouble. So, I'm a bit perplexed why it's not connecting in the script task.

Anyone have any thoughts? Thanks in advance!

Public Sub Main()
    Dim result As Integer
    Dim manager As ConnectionManager
    Dim ftpClient As FtpClientConnection
    Dim foldersList As String()
    Dim filesList As String()
    Dim var As Variables

    manager = Dts.Connections("FTP_Connection")

    ftpClient = New FtpClientConnection(manager.AcquireConnection(Nothing))

    Try
        If ftpClient.Connect() Then

            Call ftpClient.SetWorkingDirectory(Dts.Variables("ftp_path").ToString)
            Call ftpClient.GetListing(foldersList, filesList)


            ' Store files list in package variable.'
            Call Dts.VariableDispenser.LockOneForWrite("ftp_file_list", var)

            Try
                var("ftp_file_list").Value = filesList
            Finally
                Call var.Unlock()
            End Try

        End If

    Catch ex As Exception

        result = Dts.TaskResult = ScriptResults.Failure

        Call Dts.Events.FireError( _
            0, _
            String.Empty, _
            ex.Message, _
            String.Empty, _
            0)

    Finally

        Call ftpClient.Close()

    End Try

    Dts.TaskResult = ScriptResults.Success

End Sub ' Main
eAlchemist
  • 106
  • 1
  • 8
  • The code is failing to connect to the FTP site on "ftpClient.Connect()". Anyone have any thoughts? – eAlchemist Nov 21 '11 at 03:01
  • @Siva, thanks for the suggestion. Yes, I've done both of these things. Everything seems to be in order until the Script Task gets its hands on the connection. Could it be that the password isn't being saved in the connection or something? – eAlchemist Nov 21 '11 at 23:26
  • OK. Another update. It looks like this could be a problem with how the script task uses the FTP Connection Manager in a 64 bit environment. Anyone else had problems like this. Mind you, I don't get an error, but the connection fails. I don't have a 32 bit env. to test it on. – eAlchemist Nov 29 '11 at 21:22
  • Oh, here's the post I found that has me thinking this could be the issue: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/4a4d2c57-34aa-4773-81a0-ad23cc2805c1 – eAlchemist Nov 29 '11 at 21:26

2 Answers2

2

I think that you cannot directly assign the fileList value to an object variable. You need to loop through the values and assign it to a collection variable before assigning it to an SSIS package's object variable.

Answer in the following link contains a detailed explanation of how to loop through FTP files and then store them in an object variable.

How to avoid SSIS FTP task from failing when there are no files to download?

Code shown below was taken from the above link. The code looks for specific file pattern instead of downloading all the files. That is the only deviation from what you are trying to do.

VB code that can be used in SSIS 2005 and above.

Include the Imports statement Imports System.Text.RegularExpressions

Public Sub Main()
    Dim varCollection As Variables = Nothing
    Dim ftpManager As ConnectionManager = Nothing
    Dim ftpConnection As FtpClientConnection = Nothing
    Dim fileNames() As String = Nothing
    Dim folderNames() As String = Nothing
    Dim listOfFiles As Collections.ArrayList
    Dim remotePath As String = String.Empty
    Dim filePattern As String = String.Empty
    Dim regexp As Regex
    Dim counter As Integer

    Dts.VariableDispenser.LockForRead("User::RemotePath")
    Dts.VariableDispenser.LockForRead("User::FilePattern")
    Dts.VariableDispenser.LockForWrite("User::ListOfFiles")
    Dts.VariableDispenser.GetVariables(varCollection)

    Try

        remotePath = varCollection("User::RemotePath").Value.ToString()
        filePattern = varCollection("User::FilePattern").Value.ToString()

        ftpManager = Dts.Connections("FTP")
        ftpConnection = New FtpClientConnection(ftpManager.AcquireConnection(Nothing))

        ftpConnection.Connect()
        ftpConnection.SetWorkingDirectory(remotePath)
        ftpConnection.GetListing(folderNames, fileNames)            

        listOfFiles = New Collections.ArrayList()
        If fileNames IsNot Nothing Then
            regexp = New Regex("^" & filePattern & "$")
            For counter = 0 To fileNames.GetUpperBound(0)
                If regexp.IsMatch(fileNames(counter)) Then
                    listOfFiles.Add(remotePath & fileNames(counter))
                End If
            Next counter
        End If

        varCollection("User::ListOfFiles").Value = listOfFiles

        Dts.TaskResult = ScriptResults.Success

    Catch ex As Exception
        Dts.Events.FireError(-1, String.Empty, ex.ToString(), String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure
    Finally
        varCollection.Unlock()
        ftpConnection.Close()
        ftpConnection = Nothing
        ftpManager = Nothing
    End Try

    Dts.TaskResult = ScriptResults.Success
End Sub

Hope that helps.

Community
  • 1
  • 1
  • Thanks for your reply. That's funny, as I think I lifted this method off another post somewhere. I'll have a look at this method. – eAlchemist Nov 19 '11 at 18:04
  • So, you may be right about the assignment of the filenames, but it turns out that the problem was that the connection isn't even being made. Not sure what's up, but I've tested the connection outside of the script and it's working fine. – eAlchemist Nov 20 '11 at 04:42
0

It turns out that this was a problem created by 64bit mode. After I changed Run64BitRuntime = False, everything worked properly!

eAlchemist
  • 106
  • 1
  • 8