Quick Tip: Performing an FTP rename in a SSIS script task

Nigel Rivett provides a technique for moving files from one FTP directory to another, and for performing this processing from SQL Server Integration Services.

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

Downloads

Tags: , , , ,

  • 64177 views

  • Rate
    [Total: 28    Average: 4/5]
  • Anonymous

    Consequences
    Note that the password has to be retrived for use in the script task. You cannot retrieve the password from an ftp connection so it has to be in a variable. Usually (at least in my systems) this will be passed in from a config entry. If you don’t want to include it twice (once for the connection and once for the variable) you will need to set the connection entry from the variable. You cannot (at least I can’t) do it in an expression but you can set the ServerPassword on the connection from a script task.
    I’ll post the code to do this later.

    I worry that using script tasks is often a failure to find a way to use the built in tasks but seem to be finding more and more excuses.

  • Anonymous

    Consequences
    That was from me – didn’t change the name :).

  • nigelrivett

    Consequences
    Oops maybe I did but you have to be logged in for it to take?
    Anyway gives Tony sonething to clear up.

  • nigelrivett

    Script task to set FTP connection password
    This sets the FTP password from a variable.
    It is needed with the above if you don’t want to include it twice in the ssis config.

    Imports System
    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.

    Public Sub Main()

    ‘ Add your code here

    Dim vars As Variables
    Dim strFTPPassword As String
    Dts.VariableDispenser.LockOneForRead(“strFTPPassword”, vars)
    strFTPPassword = vars(“strFTPPassword”).Value.ToString()
    vars.Unlock()

    Dim con As ConnectionManager
    con = Dts.Connections(“FTP Site”)
    con.Properties(“ServerPassword”).SetValue(con, strFTPPassword)

    Dts.TaskResult = Dts.Results.Success
    End Sub

    End Class

  • Anonymous

    Help Needed
    In case the destination file for a rename exists, how could I handle this?

  • nigelrivett

    re: Help Needed
    You can check for the existance first or include a datestamp (date and time) in the name which should hopefully ensure that it is unique.

  • Anonymous

    MyFTP Control
    What is MyFTP Control?

  • Anonymous

    MyFTP Control
    What is MyFTP Control?

  • Ivan Peev

    Script Task Plus component
    I just wanted to comment we have implemented an extension of the standard Microsoft Script Task. It allows the implementation of your own script’s user interface and has better script reusability. This can can be used as alternative to implementing a full blown custom SSIS control flow task. For more information please visit: http://www.cozyroc.com/products.html

    Regards,
    Ivan

  • tgauchet

    FTP Put or Get: the easyest script
    Just one line (“Put” sample):
    My.Computer.Network.UploadFile(tmpFile, String.Format(“{0}/{1}/{2}”, FtpSiteAdress, FtpDestinationDir, FileName), FtpUserName, FtpUserPassword)

    Regards

    Thomas

  • Anonymous

    “My” Object ???
    What is this “My” object referred to in post directly above? Is this available due to some Reference that is set?

  • CHockenhull

    Newbie – How do I include wininet.dll in the Script Task??
    I did a search on wininet.dll but couldn’t find any reference. I’m thinking it’s part of one of the System objects, but can’t find any direct reference.

    Help, please.

    Thanks.

  • Girish (khadke@gmail.com)

    Custom FTP Task
    Do you have any example for creating Custome FTP Task.
    Proble:- we are behind proxy. And FTP task provoded by SSIS is not working because of that.
    Solution (Temperory) :- We create a script task and one execute process task. In script task i create dynamic bla.ftp file which has proxy commands, then stores that file somewhere on disk and stores value in variable. And in Execute process task i execute that bla.ftp file.
    Every time project comes i need to follow this steps. Is there any way i can create Custom FTP task and use it across the project? If yes then how?

    Regards
    Girish

  • Anonymous

    Dynamically selecting recent file from FTP Site
    How can I dynamically select most recent file from the ftp site to my local machine.

    Is this possible using ssis.

    Thanks
    Lalli

  • Anonymous

    Rename the file name
    How can i rename the file like AC.20080624 to AC only. pls needful help me

  • EQUINEXUS

    GUI Workaround
    There is a GUI Workaround for this. It’s ungainly but it works.

    1. Download the files to the local machine.

    2. Then use a Foreach Loop Container to enumerate through the collection of downloaded files.

    3. In the Foreach Loop Container, store the name of each file to a local variable.

    4. Place an FTP Task within the Foreach Loop Container and use that to delete each file (get the filename from the variable)

  • EQUINEXUS

    GUI Workaround
    There is a GUI Workaround for this. It’s ungainly but it works.

    1. Download the files to the local machine.

    2. Then use a Foreach Loop Container to enumerate through the collection of downloaded files.

    3. In the Foreach Loop Container, store the name of each file to a local variable.

    4. Place an FTP Task within the Foreach Loop Container and use that to delete each file (get the filename from the variable)

  • kanthi

    how to by pass a proxy
    i am behind a firewall and i am unable to access the ftp site is there anyway i can use a proxy setting to download the file from ftp site.

    please help

  • vbob

    Need help to make above code working.
    Can you help me to make the above code working. Below is my code
    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, “ftptst”, 0, “username”, “password”, 1, 0, 0)
    MsgBox(INetConn)
    RC = FtpRenameFile(INetConn, “\xxxyyyOUTPUT.txt”, “OUTPUT.hl7”)
    MsgBox(RC)
    InternetCloseHandle(INetConn)
    InternetCloseHandle(INet)
    Dts.TaskResult = Dts.Results.Success
    End Sub

    End Class

    I am not able to figure out what I am doing wrong

    Thank You in advance