Av rating:
Total votes: 14
Total comments: 15

 CODE DOWNLOAD
 send to a friend
 printer friendly version

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:

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.

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