2005 SSIS Custom Script Task to do ftpFindFirstFile

  • Shaun McGuile

    SSCarpal Tunnel

    Points: 4111

    Basically I need to check that the files on the remote ftp server are the correct files by datetime stamp.

    I can see that by using the ftpFindFirstFile function in the wininet.dll

    I can supposedly pull back info on the files in the remote directory.

    I have pulled some code off the net and messed around with it but my InternetConnect function call always returns 0 and I get no file info in my response object.

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

    Imports System

    Imports System.Runtime.InteropServices

    Imports System.nter

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    ' The execution engine calls this method when the task executes.

    ' To access the object model, use the Dts object. Connections, variables, events,

    ' and logging features are available as static members of the Dts class.

    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.


    ' To open Code and Text Editor Help, press F1.

    ' To open Object Browser, press Ctrl+Alt+J.


    Structure WIN32_FIND_DATA

    Public dwFileAttributes As UInteger

    Public ftCreationTime As System.Runtime.InteropServices.ComTypes.FILETIME

    Public ftLastAccessTime As System.Runtime.InteropServices.ComTypes.FILETIME

    Public ftLastWriteTime As System.Runtime.InteropServices.ComTypes.FILETIME

    Public nFileSizeHigh As UInteger

    Public nFileSizeLow As UInteger

    Public dwReserved0 As UInteger

    Public dwReserved1 As UInteger

    Public cFileName As String

    Public cAlternateFileName As String

    End Structure

    Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal HINet As UInteger) As UInteger

    Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _

    (ByVal sAgent As String, _

    ByVal lAccessType As UInteger, _

    ByVal sProxyName As String, _

    ByVal sProxyBypass As String, _

    ByVal lFlags As UInteger _

    ) As UInteger

    Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _

    (ByVal hInternetSession As UInteger, _

    ByVal sServerName As String, _

    ByVal nServerPort As UInteger, _

    ByVal sUsername As String, _

    ByVal sPassword As String, _

    ByVal lService As UInteger, _

    ByVal lFlags As UInteger, _

    ByVal lContext As UInteger _

    ) As UInteger

    Private Declare Function FtpRenameFile Lib "wininet.dll" Alias "FtpRenameFileA" _

    (ByVal hFtpSession As UInteger, _

    ByVal lpszExisting As String, _

    ByVal lpszNew As String _

    ) As Boolean

    Private Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _

    (ByVal hConnect As UInteger, _

    ByVal lpszSearchFile As String, _

    ByVal lpFindFileData As WIN32_FIND_DATA, _

    ByVal dwFlags As UInteger, _

    ByVal dwContext As UInteger _

    ) As Boolean

    Public Sub Main()

    Dim INet, INetConn As UInteger

    Dim RC As Boolean

    Dim vars As Variables

    Dim strFTPServer As String

    Dts.VariableDispenser.LockOneForRead("FTPServerName", vars)

    strFTPServer = vars("FTPServerName").Value.ToString()


    Dim strFTPUser As String

    Dts.VariableDispenser.LockOneForRead("FTPServerUserName", vars)

    strFTPUser = vars("FTPServerUserName").Value.ToString()


    Dim strFTPPassword As String

    Dts.VariableDispenser.LockOneForRead("FTPServerPassword", vars)

    strFTPPassword = vars("FTPServerPassword").Value.ToString()


    Dim strFTPPath As String

    Dts.VariableDispenser.LockOneForRead("CSVRemoteFilePath", vars)

    strFTPPath = vars("CSVRemoteFilePath").Value.ToString()


    Dim strFileOne As String

    Dts.VariableDispenser.LockOneForRead("datafilename", vars)

    strFileOne = vars("datafilename").Value.ToString()

    Dim FullPath As String

    FullPath = strFTPPath & strFileOne

    Dim FileData As WIN32_FIND_DATA


    INet = InternetOpen("FTP", 1, vbNullString, vbNullString, 0)

    INetConn = InternetConnect(INet, "ftpServerName", 21, "ftpUserName", "ftpPassword", 0, 0, 0)

    RC = FtpFindFirstFile(INetConn, FullPath, FileData, 0, 0)

    Catch ex As Exception


    End Try

    If RC <> True Then

    Dts.TaskResult = Dts.Results.Failure


    Dts.TaskResult = Dts.Results.Success

    End If

    End Sub

    End Class

    Anyone got any ideas?

    This is driving me nuts

    Hiding under a desk from SSIS Implemenation Work :crazy:


    One Orange Chip

    Points: 28499

    For easier solution, check third-party CozyRoc SFTP Task. It does support both SSH and FTPS connections and you can get remote file time stamp thru IFileInfo interface.

    Do not hesitate to contact us if you have questions.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • Shaun McGuile

    SSCarpal Tunnel

    Points: 4111

    Hmm spend $500 for CozyRoc.....or......

    :crazy: idea :crazy:

    I know its a windows server it has http://FTP.exe on it.....

    so I can write a batchfile to do an ftp task dir and pipe it to a text file on the calling machine.

    Then write a bit of VB.Net in a SSIS script task to decode the text file and get the info I want.

    To call the ftp batch file all I need is an Execute Process SSIS task.

    ....and well it worked! :w00t:

    Hiding under a desk from SSIS Implemenation Work :crazy:

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply