[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

Ok, this should solve both of your questions. I've created a little sample script for you that you can use to determine what properties exist for a given connection. You can then manipulate and modify these properties by using them the same what I've displayed them.

Create a DTS package with 1 ActiveX Script Task and one generic Text File Destination connection (mine is named "Sample"). Load this script into your ActiveX Script Task:

Function Main()
    dim sfilename, x, y

    dim objpkg
    set objpkg = dtsglobalvariables.parent

    y = "DTS Package Connection (Sample) Properties" & vbcrlf & vbcrlf

    for x = 1 to objpkg.connections("Sample").properties.count
        y = y & objpkg.connections("Sample").properties(x).name & ": " & objpkg.connections("Sample").properties(x).value & vbcrlf
    next

    msgbox y

    sfilename = objpkg.connections("Sample").properties("DataSourc e").value
    msgbox "Current File Name: " & sfilename

    sfilename = "C:\Testing.txt"
    msgbox "Changed File Name: " & sfilename

    objpkg.connections("Sample").properties("DataSourc e").value = sfilename

    msgbox "Filename set to new value: " & objpkg.connections("Sample").properties("DataSourc e").value

    set objpkg = nothing
    Main = DTSTaskExecResult_Success
End Function


Run that script, and it'll go through and display, change, and set a new "DataSource" for your Text File Destination connection. Let me know if you have any questions - bizzeau@hotmail.com - I've been working with DTS for just shy of FOREVER... ;)

Thanks,
Beau Frusetta
DBA/Developer

I tried your recommended script but it did not work for me. Attached in the Dts package I created with your script. I keep getting the error "sample" was not found. I created a sample text destination. Please let me know what you think.

Is "Sample" supposed to be the name of the server and DataSource the name of the database? I get the same error. This is a bit vague, can you please explain? THANK YOU!!!! :)

wow, this is very old and it has been resolved. Thank you answering.

Jaime E. Maccou