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:<\/p>\n
<\/pre>\nPublic Class ScriptMain<\/p>\n
\u00a0\u00a0 Private Declare Function InternetCloseHandle Lib “wininet.dll”
\n(ByVal HINet As Integer) As Integer<\/p>\n
\u00a0\u00a0 Private Declare Function InternetOpen Lib “wininet.dll”
\nAlias “InternetOpenA”
\n(ByVal sAgent As String,
\nByVal lAccessType As Integer,
\nByVal sProxyName As String,
\nByVal sProxyBypass As String,
\nByVal lFlags As Integer)
\nAs Integer<\/p>\n
\u00a0\u00a0 Private Declare Function InternetConnect Lib “wininet.dll”
\nAlias “InternetConnectA”
\n(ByVal hInternetSession As Integer,
\nByVal sServerName As String,
\nByVal nServerPort As Integer,
\nByVal sUsername As String,
\nByVal sPassword As String,
\nByVal lService As Integer,
\nByVal lFlags As Integer,
\nByVal lContext As Integer)
\nAs Integer<\/p>\n
\u00a0\u00a0 Private Declare Function FtpRenameFile Lib “wininet.dll”
\nAlias “FtpRenameFileA”
\n(ByVal hFtpSession As Integer,
\nByVal lpszExisting As String,
\nByVal lpszNew As String)
\nAs Boolean<\/p>\n
\u00a0\u00a0 Public Sub Main()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim INet, INetConn As Integer
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dim RC As Boolean
\n\u00a0\u00a0\u00a0\u00a0\u00a0 INet = InternetOpen(“MyFTP Control”, 1, vbNullString,
\n vbNullString, 0)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 INetConn = InternetConnect(INet, “FTPServer”, 0,
\n“UserName”, “Password”, 1, 0, 0)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 RC = FtpRenameFile(INetConn, “\/myfilepath\/myfile”,
\n“\/mydestfilepath\/myfile”)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 InternetCloseHandle(INetConn)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 InternetCloseHandle(INet)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Success<\/p>\n
\u00a0\u00a0 End Sub<\/p>\n
End Class<\/p>\n
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<\/b> file loop in the package) could be used to retrieve all information from variables in the package.<\/p>\n
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<\/b> file loop – setting the variable strMyFileNamePath<\/b> to the file path and name. The path for the file is held in strLocalPath<\/b> and used to set the FTP directory path in the replace<\/b> statement (see below).<\/p>\n
This FTP move script would be one of the last tasks in the loop, to indicate that processing for that file is complete.<\/p>\n
<\/pre>\nImports System
\nImports System.Data
\nImports System.Math
\nImports Microsoft.SqlServer.Dts.Runtime<\/p>\n
Public Class ScriptMain
\n\u00a0\u00a0 Private Declare Function InternetCloseHandle Lib “wininet.dll”
\n(ByVal HINet As Integer) As Integer
\n\u00a0\u00a0 Private Declare Function InternetOpen Lib “wininet.dll”
\nAlias “InternetOpenA”
\n(ByVal sAgent As String,
\nByVal lAccessType As Integer,
\nByVal sProxyName As String,
\nByVal sProxyBypass As String,
\nByVal lFlags As Integer
\n) As Integer
\n\u00a0\u00a0 Private Declare Function InternetConnect Lib “wininet.dll”
\nAlias “InternetConnectA”
\n(ByVal hInternetSession As Integer,
\nByVal sServerName As String,
\nByVal nServerPort As Integer,
\nByVal sUsername As String,
\nByVal sPassword As String,
\nByVal lService As Integer,
\nByVal lFlags As Integer,
\nByVal lContext As Integer
\n) As Integer
\n\u00a0\u00a0 Private Declare Function FtpRenameFile Lib “wininet.dll”
\nAlias “FtpRenameFileA”
\n(ByVal hFtpSession As Integer,
\nByVal lpszExisting As String,
\nByVal lpszNew As String
\n) As Boolean<\/p>\n
\u00a0\u00a0 Public Sub Main()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim INet, INetConn As Integer
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dim RC As Boolean<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim vars As Variables<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strMyFileNamePath As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strMyFileNamePath”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strMyFileNamePath = vars(“strMyFileNamePath”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strFTPServer As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strFTPServer”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strFTPServer = vars(“strFTPServer”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strFTPUser As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strFTPUser”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strFTPUser = vars(“strFTPUser”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strFTPPassword As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strFTPPassword”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strFTPPassword = vars(“strFTPPassword”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strFTPPath As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strFTPPath”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strFTPPath = vars(“strFTPPath”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strFTPPathProcessed As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strFTPPathProcessed”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strFTPPathProcessed = vars(“strFTPPathProcessed”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strLocalPath As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.VariableDispenser.LockOneForRead(“strLocalPath”, vars)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strLocalPath = vars(“strLocalPath”).Value.ToString()
\n\u00a0\u00a0\u00a0\u00a0\u00a0 vars.Unlock()<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strMyFileNamePathOld As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strMyFileNamePathOld = Replace<\/b>(strMyFileNamePath,
\n strLocalPath, strFTPPath)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strMyFileNamePathOld = Replace<\/b>(strMyFileNamePathOld, “\\”, “\/”)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strMyFileNamePathNew As String
\n\u00a0\u00a0\u00a0\u00a0\u00a0 strMyFileNamePathNew = Replace<\/b>(strMyFileNamePathOld,
\n strFTPPath, strFTPPathProcessed)<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 INet = InternetOpen(“MyFTP Control”, 1, vbNullString,
\n vbNullString, 0)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 INetConn = InternetConnect(INet, strFTPServer, 0, strFTPUser,
\n strFTPPassword, 1, 0, 0)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 RC = FtpRenameFile(INetConn, strMyFileNamePathOld,
\n strMyFileNamePathNew)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 InternetCloseHandle(INetConn)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 InternetCloseHandle(INet)<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 MsgBox(strMyFileNamePathOld & “_” & strMyFileNamePathNew)<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 If RC <> True Then
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Failure
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Else
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Success
\n\u00a0\u00a0\u00a0\u00a0\u00a0 End If
\n\u00a0\u00a0 End Sub<\/p>\n
End Class<\/p>\n","protected":false},"excerpt":{"rendered":"
Nigel Rivett provides a technique for moving files from one FTP directory to another, and for performing this processing from SQL Server Integration Services.…<\/p>\n","protected":false},"author":223267,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4612,4613,4150,4151,4306],"coauthors":[48340],"class_list":["post-213","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-ftp-rename","tag-processing-ftp-files","tag-sql","tag-sql-server","tag-ssis"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/213","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/223267"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=213"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"predecessor-version":[{"id":74818,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/213\/revisions\/74818"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=213"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}