开发者

Using a script task in SSIS with C# language, how to convert a DTS ActiveX script that creates files and then appends the full file names

I'm trying to convert a DTS process to SSIS and I want to convert a DTS script task that creates 3 files (only creates the file if there’s data to write to it) and then appends the full file names to a variable, which is then copied to a global variable. Since I'm new to this I would like a step by step demonstation if possible, Please, Please, Pretty Please help...Thanks. Below is an example of the DTS script that creates two instead of three files:

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 I开发者_JAVA百科S 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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜