SSIS 脚本任务 - 无法填充数据表

Question

我创建了一个包来从 FTP 服务器导入文件，并且只需要处理新文件，即排除已加载的文件（保存在表中）。

我首先运行执行 SQL 任务来填充“AlreadyLoadedFiles”对象变量。然后，我尝试确定需要在脚本任务中处理哪些文件。我首先加载 FTP 服务器上的文件名称，然后删除那些已加载的文件。

我在检索 FTP 上的文件名时没有问题，但问题是当我用对象变量“AlreadyLoadedFiles”填充 OleDBDataAdapter 时，生成的数据表为空，我不确定为什么。

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Data.OleDb
Imports System.Collections.Specialized
Imports System.Text.RegularExpressions

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()
        '

        Dim vs As Variables
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter()


        'We need to go to or FTP server
        Dts.VariableDispenser.LockOneForRead("FTPSourceDirectory", vs)

        Dim cm As ConnectionManager = Dts.Connections("FTPServer")
        Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
        ftp.Connect()
        ftp.SetWorkingDirectory(vs("FTPSourceDirectory").Value.ToString())
        vs.Unlock()

        'We now need to get ourselves the files we have already seen
        Dts.VariableDispenser.LockOneForRead("AlreadyLoadedFiles", vs)


        da.Fill(dt, vs("AlreadyLoadedFiles").Value)
        MessageBox.Show(dt.Rows.Count)
        vs.Unlock()


        Dim foldernames() As String
        Dim filenames() As String

        'Get the list of files that are there on the FTP server
        ftp.GetListing(foldernames, filenames)

        Dim dr As DataRow

        Dim ss As StringCollection = New StringCollection()

        Dim iFileCount As Integer

        If filenames Is Nothing Then
            MessageBox.Show("No Files Found")
            Exit Sub
        Else
            'Need to loop through all the files found
            For iFileCount = 0 To filenames.GetUpperBound(0)

                'First we add all of the found files to the Array (Object) but only if they are CSV files

                Dim re As New Regex("^co_users_report_\d{4}-\d{2}-\d{2}\.csv$")

                If re.IsMatch(filenames(iFileCount).ToString()) Then
                    ss.Add(filenames(iFileCount).ToString())
                    Dts.Events.FireInformation(0, "", filenames(iFileCount).ToString(), "", 0, True)
                End If
                For Each dr In dt.Rows
                    Dts.Events.FireInformation(0, "", dr(0).ToString(), "", 0, True)
                    If dr(0).ToString() = filenames(iFileCount).ToString() Then
                        MessageBox.Show(dr(0).ToString)
                        Dts.Events.FireInformation(0, "", "Removed " & filenames(iFileCount).ToString() & " from array because it was previously loaded.", "", 0, True)

                        ss.Remove(filenames(iFileCount).ToString())
                        Exit For
                    End If
                Next
            Next
        End If

        Dts.VariableDispenser.LockOneForWrite("FilesForFTPDownload", vs)

        vs(0).Value = ss


        vs.Unlock()

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

我正在使用 SQL Server (SSIS) 2008 R2 64 位

Answer 1

我建议您将其分解为多个组件。有一个脚本任务，如上面的任务，从 ftp 站点提取必要的数据并将其保存为平面文件。然后构建使用数据流任务来读取平面文件并进行相应的处理。虽然您这样做的方式是有效的，但我认为如果您使用其他 SSIS 组件，它的效率可能会更高。

Answer 2

我实际上发现了问题 - 这是正在重置 ADO Rowset 的审核框架......