脚本任务错误 - 无法将 GetListings 文件列表放入对象变量中

Question

使用下面的脚本任务将文件列表填充到 User::ftp_file_list 中。但是，下一步中的 For Each 循环容器会返回错误“Foreach 循环容器：变量“User::ftp_file_list”中的对象不包含枚举器”。

好的，事实证明问题是未建立连接。我不明白的是，我已经独立测试了连接，没有任何问题。所以，我有点困惑为什么它没有在脚本任务中连接。

有人有什么想法吗？提前致谢！

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

Answer 1

我认为您不能直接将 fileList 值分配给对象变量。您需要循环遍历这些值并将其分配给集合变量，然后再将其分配给 SSIS 包的对象变量。

以下链接中的答案包含如何循环遍历 FTP 文件然后将它们存储在对象变量中的详细说明。

当没有文件可供下载时，如何避免 SSIS FTP 任务失败？

下面显示的代码取自上面的链接。该代码查找特定的文件模式，而不是下载所有文件。这是与您想要做的事情的唯一偏差。

可在 SSIS 2005 及更高版本 中使用的 VB 代码。

包含 Imports 语句 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

希望有帮助。

Answer 2

事实证明这是64位模式造成的问题。我更改 Run64BitRuntime = False 后，一切正常！