DTS: VBScript tests file creation month at the ftp site

Option Explicit

'  This VBScript tests the file creation month at the ftp site.
'
' reference: http://www.microsoft.com/technet/scriptcenter/resources/tales/sg1002.mspx
' reference: http://www.sqlservercentral.com/articles/Administering/usingvbscripttoautomatetasks/1171/
'
Function Main()
    Dim fso
    Dim temp_folder
    Dim ftp_control_file
    Dim ftp_control_filename
    Dim shell
    Dim return
    
    
    Set fso         = CreateObject("Scripting.FileSystemObject")
    Set temp_folder = fso.GetSpecialFolder(2) ' Temporary Folder = 2
        
    ' ftp control file name
    ftp_control_filename = temp_folder.Path + "\" + fso.GetTempName()
    
    ' create ftp control file
    Set ftp_control_file = fso.CreateTextFile(ftp_control_filename, True)
    ftp_control_file.WriteLine ("open ftp.xyz.com")
    ftp_control_file.WriteLine ("my_user_name")
    ftp_control_file.WriteLine ("my_password")
    ftp_control_file.WriteLine ("ascii")
    ftp_control_file.WriteLine ("dir my_file_name.txt")
    ftp_control_file.WriteLine ("bye")
    ftp_control_file.Close
    Set ftp_control_file = Nothing
    
    ' reference: http://www.printdistributor.com/forum/post/124
    ' The WScript object isn't part of DTS VBScript.
    Set shell = CreateObject( "WScript.Shell" )
    ' reference: http://www.chebucto.ns.ca/~ak621/DOS/ExitCode.html
    ' the return variable will be the return value from the last FIND command
    return = shell.Run("%comspec% /c ftp -s:"""  & ftp_control_filename & """ | find ""my_file_name.txt"" | find "" " & MonthName(Month(Now), True) & " """, 0, True)
    Set shell = Nothing
    
    ' clean up control file
    fso.DeleteFile ftp_control_filename
    
    Set temp_folder = Nothing
    Set fso         = Nothing
    
    ' return the flag to the DTS package
    If return = 0 Then
        Main = DTSTaskExecResult_Success
    Else
        Main = DTSTaskExecResult_Failure
    End If
End Function
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s