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

Downloading a file over HTTP the SSIS way

http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

by Darren Green

Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()

        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)

        ' Create a new HTTP client connection
        Dim connection As New HttpClientConnection(nativeObject)


        ' Download the file #1
        ' Save the file from the connection manager to the local path specified
        Dim filename As String = "C:\Temp\Sample.txt"
        connection.DownloadFile(filename, True)

        ' Confirm file is there
        If File.Exists(filename) Then
            MessageBox.Show(String.Format("File {0} has been downloaded.", filename))
        End If


        ' Download the file #2
        ' Read the text file straight into memory
        Dim buffer As Byte() = connection.DownloadData()
        Dim data As String = Encoding.ASCII.GetString(buffer)

        ' Display the file contents
        MessageBox.Show(data)


        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

http://support.microsoft.com/kb/135975
http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

FTP client

'
'    This class is a FTP client. It provides limited features and is only supporting passive mode.
'    It works well with logging in to the proxy server.
'
'    Written By: Peter Ng
'    Date Last Modified: 2013.07.31
'
'    Ref:
'    (1) Network Programming in .NET: With C# and Visual Basic .NET by Fiach Reid
'    (2) http://www.codeproject.com/Articles/293391/File-Transfer-Protocol-FTP-Client
'

Imports System
Imports System.Collections.Generic
Imports System.IO
Imports System.Net.Sockets
Imports System.Text

Public Class TinyFTP
    Private Const BUFFER_SIZE As Long = 4096

    Private ms_host As String
    Private mi_port As Integer
    Private ms_username As String
    Private ms_password As String
    Private mi_timeout_ms As Integer

    Private m_command_connection As TcpClient
    Private m_command_stream As NetworkStream

    Public Sub New()
    End Sub

    Public Sub New(ByVal host As String, ByVal port As Integer, ByVal username As String, ByVal password As String)
    Me.New(host, port, username, password, 30)
    End Sub

    Public Sub New(ByVal host As String, ByVal port As Integer, ByVal username As String, ByVal password As String, ByVal timeout As Integer)
    Open(host, port, username, password, timeout)
    End Sub

    Public Sub Open(ByVal host As String, ByVal port As Integer, ByVal username As String, ByVal password As String, ByVal timeout As Integer)
    ms_host = host
    mi_port = port
    ms_username = username
    ms_password = password
    mi_timeout_ms = timeout * 1000

    Connect()
    End Sub

    Public Sub Close()
    If m_command_stream IsNot Nothing Then
    m_command_stream.Close()
    m_command_stream = Nothing
    End If

    If m_command_connection IsNot Nothing Then
    m_command_connection.Close()
    m_command_connection = Nothing
    End If
    End Sub

    Public Sub Connect()
    Close()

    '
    ' open connection
    '
    m_command_connection = New TcpClient(ms_host, mi_port)
    m_command_connection.ReceiveTimeout = mi_timeout_ms
    m_command_connection.SendTimeout = mi_timeout_ms
    m_command_stream = m_command_connection.GetStream()
    Dim reply As String = (New StreamReader(m_command_stream)).ReadLine()
    If reply.Substring(0, 3)  "220" Then
    Throw New IOException(reply)
    End If

    '
    ' login
    '
    reply = send_ftp_command("USER " + ms_username)
    Select Case reply.Substring(0, 3)
    Case Is = "230"
    ' user logged in and does not need password

    Case Is = "331"
    ' password needed 
    reply = send_ftp_command("PASS " & ms_password)
    If Not (reply.Substring(0, 3) = "230" OrElse reply.Substring(0, 3) = "202") Then
    Throw New IOException(reply)
    End If

    Case Else
    Throw New IOException(reply)
    End Select
    End Sub

    Public Sub Change_Directory(ByVal Path As String)
    If Not (Path = "" OrElse Path = ".") Then
    Dim reply As String = send_ftp_command("CWD " + Path)
    If Not reply.Substring(0, 3) = "250" Then
    Throw New IOException(reply)
    End If
    End If
    End Sub

    Public Sub Download(ByVal filename As String, ByVal local_path As String)
    Set_Binary()

    ' prepare file stream
    Dim fs As FileStream = New FileStream(local_path, FileMode.CreateNew)

    Dim reply As Collection

    Try
    reply = send_passive_ftp_command("RETR " + filename, CType(fs, Stream))
    Catch ex As Exception
    Throw ex
    Finally
    fs.Close()
    End Try

    Dim s As String
    For Each s In reply
    Select Case s.Length
    Case 0
    ' fine
    Case Is < 3
    ' unexpected problems
    Throw New IOException(s)
    Case Else
    Dim m As String = s.Substring(0, 3)
    If Not (m = "150" OrElse m = "226" OrElse m = "125") Then
    Throw New IOException(m)
    End If
    End Select
    Next
    End Sub

    Public Function Filename_List() As String()
    Return Filename_List("")
    End Function

    Public Function Filename_List(ByVal directory As String) As String()
    Dim ms As MemoryStream = New MemoryStream()

    ' prepare FTP command
    Dim cmd As String = "NLST"
    If Not String.IsNullOrEmpty(directory) Then
    cmd = cmd + " " + directory
    End If

    Dim reply As Collection = send_passive_ftp_command(cmd, CType(ms, Stream))

    Dim s As String
    For Each s In reply
    Select Case s.Length
    Case 0
    ' fine
    Case Is < 3
    ' unexpected problems
    Throw New IOException(s)
    Case Else
    Dim m As String = s.Substring(0, 3)
    If Not (m = "150" OrElse m = "226" OrElse m = "125") Then
    Throw New IOException(m)
    End If
    End Select
    Next

    Dim mr As StreamReader = New StreamReader(ms)
    ms.Position = 0
    Return mr.ReadToEnd().Split(New String() {vbCrLf, vbLf}, StringSplitOptions.RemoveEmptyEntries)
    End Function

    Public Function File_Properties(ByVal filename As String) As Dictionary(Of String, Object)
    Dim ms As MemoryStream = New MemoryStream()

    ' validate file name
    If String.IsNullOrEmpty(filename) Then
    Throw New Exception("missing file name")
    ElseIf filename.Contains("*") Then
    Throw New Exception("the method does not support wildcard")
    End If

    ' prepare FTP command
    Dim cmd As String = "LIST" + " " + filename

    Dim reply As Collection = send_passive_ftp_command(cmd, CType(ms, Stream))

    Dim s As String
    For Each s In reply
    Select Case s.Length
    Case 0
    ' fine
    Case Is < 3
    ' unexpected problems
    Throw New IOException(s)
    Case Else
    Dim m As String = s.Substring(0, 3)
    If Not (m = "150" OrElse m = "226" OrElse m = "125") Then
    Throw New IOException(m)
    End If
    End Select
    Next

    Dim mr As StreamReader = New StreamReader(ms)
    ms.Position = 0
    ' expecting only one line
    Dim lines As String() = mr.ReadToEnd().Split(New String() {vbCrLf, vbLf}, StringSplitOptions.RemoveEmptyEntries)
    If lines.Length  1 Then
    Throw New Exception("invalid number of files")
    End If

    Dim parts As String() = lines(0).Split(New String() {" "}, StringSplitOptions.RemoveEmptyEntries)
    If parts.Length  0

    ' read command port for messages
    Dim command_stream_reader As StreamReader = New StreamReader(command_stream)
    Dim c As Collection = New Collection
    While command_stream.DataAvailable
    c.Add(command_stream_reader.ReadLine())
    End While

    Return c
    End Function

    Private Function create_passive_connection() As TcpClient
    Dim reply As String = send_ftp_command("PASV")
    If reply.Substring(0, 3)  "227" Then
    Throw New IOException(reply)
    End If

    ' sample FTP replay
    ' 227 Entering Passive Mode (127,0,0,1,4,147).
    Dim index1 As Integer = reply.IndexOf("("c)
    Dim index2 As Integer = reply.IndexOf(")"c, index1)
    Dim values As String() = reply.Substring(index1 + 1, index2 - index1 - 1).Split(New Char() {","c})

    Dim ip As String = values(0) + "." + values(1) + "." + values(2) + "." + values(3)
    Dim high_byte As Integer = Integer.Parse(values(4)) * 256
    Dim low_byte As Integer = Integer.Parse(values(5))
    Dim port As Integer = high_byte + low_byte

    Dim cnn As TcpClient = New TcpClient(ip, port)
    cnn.ReceiveTimeout = mi_timeout_ms
    cnn.SendTimeout = mi_timeout_ms
    Return cnn
    End Function

    Protected Overrides Sub Finalize()
    Me.Close()
    MyBase.Finalize()
    End Sub
End Class

.Net 2.0 FTPWebRequest error

In case the FTP PASV command – the FTP protocol command for the passive mode – is casted behind the firewall, the proxy server, or the DSL router, the FTP server will more likely return an address different from the computer casted the command. The returned address is the IP facing the outside world. It is normal and very common. Most FTP clients, even Microsoft very own FTP client ftp.exe, are ok with it. Too bad, the .Net class FtpWebRequest will report the following error. And, they say it is for the security reason.

“The server returned an address in response to the PASV command that is different than the address to which the FTP connection was made”

The problem is expected to be fixed in NETFX 4.0.

http://connect.microsoft.com/VisualStudio/feedback/details/97409/ftpwebrequest-passive-mode-with-different-data-ip