A common requirement is to process files from an FTP directory. Often this is done by processing every file in the directory and moving each file to an archive directory after processing. Therefore, on the next run, the “live” directory will only contain unprocessed files and the “archive” directory will provide a record of the files processed.
The FTP command to move files is “rename”, in effect renaming the file path. Unfortunately a rename isn’t included in the commands available in the FTP task in SQL Server Integration Services (SSIS). This is quite a problem for a project that wishes to keep all processing within SSIS.
Implementing a single FTP instruction in a script task is pretty straightforward, but a search for code to implement this “rename” repeatedly turned up an implementation of an FTP class – giving a complete instruction set wrapper and interpreting return codes.
However, I finally found a simple FTP script to perform a file get here:
http://p2p.wrox.com/topic.asp?TOPIC_ID=1500
All I needed to do was change the command and I had a technique that accomplished FTP renames very easily. The following script will move a hard coded file:
1 |
Public Class ScriptMain
Private Declare Function InternetCloseHandle Lib “wininet.dll”
(ByVal HINet As Integer) As Integer
Private Declare Function InternetOpen Lib “wininet.dll”
Alias “InternetOpenA”
(ByVal sAgent As String,
ByVal lAccessType As Integer,
ByVal sProxyName As String,
ByVal sProxyBypass As String,
ByVal lFlags As Integer)
As Integer
Private Declare Function InternetConnect Lib “wininet.dll”
Alias “InternetConnectA”
(ByVal hInternetSession As Integer,
ByVal sServerName As String,
ByVal nServerPort As Integer,
ByVal sUsername As String,
ByVal sPassword As String,
ByVal lService As Integer,
ByVal lFlags As Integer,
ByVal lContext As Integer)
As Integer
Private Declare Function FtpRenameFile Lib “wininet.dll”
Alias “FtpRenameFileA”
(ByVal hFtpSession As Integer,
ByVal lpszExisting As String,
ByVal lpszNew As String)
As Boolean
Public Sub Main()
Dim INet, INetConn As Integer
Dim RC As Boolean
INet = InternetOpen(“MyFTP Control”, 1, vbNullString,
vbNullString, 0)
INetConn = InternetConnect(INet, “FTPServer”, 0,
“UserName”, “Password”, 1, 0, 0)
RC = FtpRenameFile(INetConn, “/myfilepath/myfile”,
“/mydestfilepath/myfile”)
InternetCloseHandle(INetConn)
InternetCloseHandle(INet)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Of course, for a real world implementation we need to get the FTP site credentials, file paths and file names from variables in the SSIS package. The following code (which would be part of a for each file loop in the package) could be used to retrieve all information from variables in the package.
The complete package would retrieve all files from the FTP directory with an FTP task and then process each of the files with a for each file loop – setting the variable strMyFileNamePath to the file path and name. The path for the file is held in strLocalPath and used to set the FTP directory path in the replace statement (see below).
This FTP move script would be one of the last tasks in the loop, to indicate that processing for that file is complete.
1 |
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Private Declare Function InternetCloseHandle Lib “wininet.dll”
(ByVal HINet As Integer) As Integer
Private Declare Function InternetOpen Lib “wininet.dll”
Alias “InternetOpenA”
(ByVal sAgent As String,
ByVal lAccessType As Integer,
ByVal sProxyName As String,
ByVal sProxyBypass As String,
ByVal lFlags As Integer
) As Integer
Private Declare Function InternetConnect Lib “wininet.dll”
Alias “InternetConnectA”
(ByVal hInternetSession As Integer,
ByVal sServerName As String,
ByVal nServerPort As Integer,
ByVal sUsername As String,
ByVal sPassword As String,
ByVal lService As Integer,
ByVal lFlags As Integer,
ByVal lContext As Integer
) As Integer
Private Declare Function FtpRenameFile Lib “wininet.dll”
Alias “FtpRenameFileA”
(ByVal hFtpSession As Integer,
ByVal lpszExisting As String,
ByVal lpszNew As String
) As Boolean
Public Sub Main()
Dim INet, INetConn As Integer
Dim RC As Boolean
Dim vars As Variables
Dim strMyFileNamePath As String
Dts.VariableDispenser.LockOneForRead(“strMyFileNamePath”, vars)
strMyFileNamePath = vars(“strMyFileNamePath”).Value.ToString()
vars.Unlock()
Dim strFTPServer As String
Dts.VariableDispenser.LockOneForRead(“strFTPServer”, vars)
strFTPServer = vars(“strFTPServer”).Value.ToString()
vars.Unlock()
Dim strFTPUser As String
Dts.VariableDispenser.LockOneForRead(“strFTPUser”, vars)
strFTPUser = vars(“strFTPUser”).Value.ToString()
vars.Unlock()
Dim strFTPPassword As String
Dts.VariableDispenser.LockOneForRead(“strFTPPassword”, vars)
strFTPPassword = vars(“strFTPPassword”).Value.ToString()
vars.Unlock()
Dim strFTPPath As String
Dts.VariableDispenser.LockOneForRead(“strFTPPath”, vars)
strFTPPath = vars(“strFTPPath”).Value.ToString()
vars.Unlock()
Dim strFTPPathProcessed As String
Dts.VariableDispenser.LockOneForRead(“strFTPPathProcessed”, vars)
strFTPPathProcessed = vars(“strFTPPathProcessed”).Value.ToString()
vars.Unlock()
Dim strLocalPath As String
Dts.VariableDispenser.LockOneForRead(“strLocalPath”, vars)
strLocalPath = vars(“strLocalPath”).Value.ToString()
vars.Unlock()
Dim strMyFileNamePathOld As String
strMyFileNamePathOld = Replace(strMyFileNamePath,
strLocalPath, strFTPPath)
strMyFileNamePathOld = Replace(strMyFileNamePathOld, “\”, “/”)
Dim strMyFileNamePathNew As String
strMyFileNamePathNew = Replace(strMyFileNamePathOld,
strFTPPath, strFTPPathProcessed)
INet = InternetOpen(“MyFTP Control”, 1, vbNullString,
vbNullString, 0)
INetConn = InternetConnect(INet, strFTPServer, 0, strFTPUser,
strFTPPassword, 1, 0, 0)
RC = FtpRenameFile(INetConn, strMyFileNamePathOld,
strMyFileNamePathNew)
InternetCloseHandle(INetConn)
InternetCloseHandle(INet)
MsgBox(strMyFileNamePathOld & “_” & strMyFileNamePathNew)
If RC <> True Then
Dts.TaskResult = Dts.Results.Failure
Else
Dts.TaskResult = Dts.Results.Success
End If
End Sub
End Class
Load comments