FSO Help

[dzdncnfsd]

I searched for help on this subject but nothing addressed my particular problem. I need to import data from a text file on a daily basis, but the 'source' file name changes every day because the date is embedded in the file name. I am posting the code that was originally created with VB Script as a step in a DTS package to see if anyone can help. The package runs successfully on the workstation that it was created on as long as the creator is logged in. He cannot run it on the SQL server box or anyone else's workstation, and no one else can log in and run it on his workstation. We all have the same permissions, have the same version of msdadc.dll, oledb32.dll, and scrrun.dll on our workstations. Sorry for the long post, and thanks in advance for any help received.
'***********************************************************
' Visual Basic ActiveX Script
'***********************************************************
Function Main()

Dim objFSO
Dim cnADODB
Dim cmdADOBD
Dim rs
Dim strFileName
Dim oPKG
Dim cn

Set cnADODB = CreateObject("ADODB.Connection&quot
Set cmdADODB = CreateObject("ADODB.Command&quot
Set objFSO = CreateObject("Scripting.FileSystemObject&quot
cnADODB.connectionTimeout = 0
cnADODB.Open "driver={SQL Server};server={ServerName};database={DBname};uid=sa"
cmdADODB.ActiveConnection = cnADODB
cmdADODB.Properties.Item("Command Time Out&quot.Value=0

' Set rs = cnADODB.Execute("SELECT DISTINCT [MM-TIMESTAMP] as LastDate FROM DBname.dbo.TableName&quot
Set rs = cnADODB.Execute("SELECT Max(TableName.[MM-TIMESTAMP]) as Lastdate FROM DBName.dbo.TableName&quot
if WeekdayName( Weekday( date, vbSunday ), False ) = "Tuesday" Then
NextDate = rs("LastDate&quot+3
else
NextDate = rs("LastDate&quot+1
end if
' msgbox WeekdayName( Weekday( date, vbSunday ), False )
NextDateNoMask = left(cstr(NextDate),2) & Mid(cstr(NextDate),4,2) & Right(cstr(NextDate),2)
' msgbox NextDate
' msgbox NextDateNoMask
strFileName = "\\server\folder\MM" & NextDateNoMask & ".txt"

If objFSO.FileExists(strFileName) Then
' MsgBox "File Exists"
' MsgBox strFileName
DTSGlobalVariables("gvFileNameDate&quot.value = NextDate
rs.Close
cnADODB.Close
Set cnADODB = Nothing
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)&quot
cn.DataSource = strFileName
' MsgBox DTSGlobalVariables("gvFileNameDate&quot.value
Main = DTSTaskExecResult_Success

Else
' MsgBox "File Does Not Exist"
Main = DTSTaskExecResult_Failure
End If

End Function


Gladys Clemmer
gladys.clemmer@fifsg.com