vb.net - SSIS包中的脚本任务到FTP

Question

所有，

我有一个SSIS软件包(Sql Server 2008)，该软件包执行一些数据流和文件操作，然后最终将文件上传到FTP服务器。

我能够完成大部分工作，但最后一项至关重要。使用“数据流”，将使用数据库中的数据生成一个文本文件。现在，该文件的末尾带有时间戳压缩:“filename_08132012.zip”。

时间戳每天都会更改，因此每次都会将其新文件上传到FTP服务器。因此，我有一个“脚本任务”，而不是“FTP任务”(我想不出一种方法来完成这项工作)，它可以查找具有今天日期的文件并将其上载到FTP服务器。以下是代码，但是与此相关的两个问题是:

我希望此“脚本任务”能够提取当天的文件，例如:filename_08132012.zip，filename_08142012.zip。我有将日期转换为具有正确格式的字符串的代码。但是由于某种原因，事实并非如此。

如何逐步执行此脚本文件，Visual Studio允许使用VB.net代码吗？此脚本任务GUI不允许逐步执行。

它将空文件上传到FTP服务器。一个0字节的文件。我该如何解决？

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

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
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()
    '
    ' Add your code here
    '

    Try

        'Create the connection to the ftp server

        Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

        'Set the properties like username & password

        cm.Properties("ServerName").SetValue(cm, "172.24.97.21")

        cm.Properties("ServerUserName").SetValue(cm, "bbxuser")

        cm.Properties("ServerPassword").SetValue(cm, "blockbuster")

        cm.Properties("ServerPort").SetValue(cm, "21")

        cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

        cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

        cm.Properties("Retries").SetValue(cm, "1")

        'create the FTP object that sends the files and pass it the connection created above.

        Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

        'Connects to the ftp server

        ftp.Connect()

        'Build a array of all the file names that is going to be FTP'ed (in this case only one file)

        Dim files(0) As String


        Dim FileDate As Date
        FileDate = System.DateTime.Today

        files(0) = "D:\NCR\Apps\RBX.SSIS.BBX_Customer_Extract\Email_Campaign\Redbox_EmailCampaign_and_Analytics_Extract_" + FileDate.ToString("mmddyyyy") + ".zip"

        'ftp the file

        'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.

        ftp.SendFiles(files, "/Email Campaign", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII

        ftp.Close()

    Catch ex As Exception

        Dts.TaskResult = ScriptResults.Failure

    End Try

    Dts.TaskResult = ScriptResults.Success
End Sub

末级

Answer 1

在我提出问题之后，我立即想到了几件事。

我使用了不正确的格式。正确的格式是FileDate.ToString(“MMddyyyy”)+“.zip”

files(0) = "D:\NCR\Apps\RBX.SSIS.BBX_Customer_Extract\Email_Campaign\Redbox_EmailCampaign_and_Analytics_Extract_" + FileDate.ToString("mmddyyyy") + ".zip"
files(0) = "D:\NCR\Apps\RBX.SSIS.BBX_Customer_Extract\Email_Campaign\Redbox_EmailCampaign_and_Analytics_Extract_" + FileDate.ToString("MMddyyyy") + ".zip"

(见上文)

我刚刚找到了它，它起作用了:Troubleshoot Script Task

解决1和2，解决3