2005 SSIS Custom Script Task to do ftpFindFirstFile

Question

2005 SSIS Custom Script Task to do ftpFindFirstFile

Shaun McGuile

SSCarpal Tunnel

Points: 4141
More actions
May 14, 2009 at 3:47 am

#137737

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()
vars.Unlock()
Dim strFTPUser As String
Dts.VariableDispenser.LockOneForRead("FTPServerUserName", vars)
strFTPUser = vars("FTPServerUserName").Value.ToString()
vars.Unlock()
Dim strFTPPassword As String
Dts.VariableDispenser.LockOneForRead("FTPServerPassword", vars)
strFTPPassword = vars("FTPServerPassword").Value.ToString()
vars.Unlock()
Dim strFTPPath As String
Dts.VariableDispenser.LockOneForRead("CSVRemoteFilePath", vars)
strFTPPath = vars("CSVRemoteFilePath").Value.ToString()
vars.Unlock()
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
Try
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
MsgBox(ex.Message)
End Try
If RC <> True Then
Dts.TaskResult = Dts.Results.Failure
Else
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:

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

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

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: 4141 More actions · Answer 2

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: