在 SSIS 中使用 C# 语言的脚本任务，如何转换创建文件然后附加完整文件名的 DTS ActiveX 脚本

Question

我正在尝试将 DTS 进程转换为 SSIS，并且我想转换创建 3 个文件的 DTS 脚本任务（仅在有数据要写入时才创建文件），然后将完整文件名附加到变量，即然后复制到全局变量。由于我是新手，如果可能的话，我想要一步一步的演示，拜托，拜托，漂亮请帮忙......谢谢。下面是创建两个而不是三个文件的 DTS 脚本示例：

Function Main()
DIM dbConnection, rsGetFileDetails, rsGetInfo
DIM fsoFileSystem, fExportFile
DIM sGroupCode, sSequenceNumber, sFileIdentifier, FileName
DIM sLineItem, sAttachmentPaths
DIM  FileLocation
sAttachmentPaths = ""
FileLocation = DTSGlobalVariables("FileDestPath").Value

'****************************************************
'*********Connect to Database******************
'****************************************************
SET dbConnection = CreateObject( "ADODB.Connection" )
dbConnection.Provider = "sqloledb"
dbConnection.Properties("Data Source").Value = "Server"
dbConnection.Properties("Initial Catalog").Value = "Database"
dbConnection.Properties("Integrated Security").Value = "1234"
dbConnection.Open 

'*******************************************************************
'***********GET TRACE ALERT DELIST DATA************
'*******************************************************************


SET fsoFileSystem = CreateObject( "Scripting.FileSystemObject" )
SET rsGetInfo = CreateObject( "ADODB.recordset" )


sql =  "SELECT     tblExample1.IDNumber, tblExample2.First_Name, tblExample3.Main_Name FROM         tblExample1 INNER JOIN tblExample2 ON tblExample1.IDNumber = tblExample2.Entity_ID_Number WHERE     (tblExample1.IDNumber = '2') AND (Process_Date IS NULL)"

rsGetInfo.Open sql, dbConnection
IF rsGetInfo.EOF THEN
ELSE

FileName = "MyFileName_" & Replace( Date() , "/" , "")
'//Create the file
SET fExportFile = fsoFileSystem.CreateTextFile( FileLocation & FileName & ".txt", true )
DTSGlobalVariables("FileLocation").Value =  FileLocation & FileName & ".txt"    

    rsGetInfo.MoveFirst
    DO WHILE NOT  rsGetInfo.EOF OR rsGetInfo.BOF
        sLineItem = ""
        sLineItem = rsGetInfo("IDNumber") & vbtab & rsGetDelistInfo("First_Name") & vbtab & rsGetInfo("Main_Name")

        fExportFile.Write(sLineItem & vbcrlf)

        rsGetInfo.MoveNext
    LOOP
    '// Set Attachment Path     
    sAttachmentPaths = FileLocation & FileName & ".txt" 
END IF


'*******************************************************************
'***********GET DEFAULT DELIST DATA************
'*******************************************************************


SET fsoFileSystem = CreateObject( "Scripting.FileSystemObject" )
SET rsGetInfo = CreateObject( "ADODB.recordset" )


sql =  "SELECT     Contract_No FROM      tblfunny WHERE     (funnyNumb = 1) and (Process_Date IS NULL)"

rsGetInfo.Open sql, dbConnection

IF rsGetInfo.EOF THEN
ELSE

    FileName = "MyFileName_" & Replace( Date() , "/" , "")
    '//Create the file
    SET fExportFile = fsoFileSystem.CreateTextFile(  FileLocation & FileName & ".txt", true )
    DTSGlobalVariables("FileLocation").Value =  FileLocation & FileName & ".txt"    
    rsGetInfo.MoveFirst

    DO WHILE NOT  rsGetInfo.EOF OR rsGetInfo.BOF
        sLineItem = ""
        sLineItem = rsGetInfo("Contract_No")

        fExportFile.Write(sLineItem & vbcrlf)

        rsGetInfo.MoveNext
    LOOP
    '// Set Attachment Path
    IF sAttachmentPaths = "" THEN
        sAttachmentPaths = FileLocation & FileName & ".txt"
    ELSE
        sAttachmentPaths = sAttachmentPaths & "; "& FileLocation & FileName & ".txt"
    END IF
END IF
Main = DTSTaskExecResult_Success 

End Function