View Single Post
Old April 23rd, 2004, 10:27 AM
jemacc jemacc is offline
Friend of Wrox
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default DTS Visual Basic ActiveX Script

[u]This is a two part question.. Part I</u>Can someone help with this issue. See script below. I will like to add at the point of creating this file to increment the text file generated by (1) each time the DTS Package runs, for example: At 12:00 PM a file is created (20040423_trans.txt)then it is run again at 12:15 PM, I will like to keep the same file name structure for that day and increase the count to specify the amount of time it was run ex: 20040423_trans_01.txt, 20040423_trans_02.txt, etc., Any and all help will be appreciated.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Option Explicit

Function Main()

            Dim oConn, sFilename

            ' Filename format - yymmdd_webcredits.txt
            sFilename = Right(DAY(Now()), 10) &"_"&"webcredits"
            If Month(Now()) < 10 Then sFilename = "0" & _
                Month(Now()) & sFilename Else sFilename = DAY(Now()) & sFilename
            If Day(Now()) < 10 Then sFilename = _
                "0" & month(Now()) & sFilename Else sFilename = YEAR(Now()) & sFilename
            sFilename = DTSGlobalVariables("LogFilePath").Value & _
                sFilename & ".txt"

            Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
            oConn.DataSource = sFilename
            Set oConn = Nothing
            Main = DTSTaskExecResult_Success

End Function

Part II

How do I set the file path using the global variable

sFilename = DTSGlobalVariables("LogFilePath").Value & _
                sFilename & ".txt"
Jaime E. Maccou