I could not find the exact details on how to create a SSIS script that would ftp files on these forums, so I am adding my code to help save time for anyone else that might be wanting to do something similar. Here is the VB code for my script task to FTP files (hope this helps someone): ' 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.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "Enter your Server Name here") cm.Properties("ServerUserName").SetValue(cm, "Enter your FTP User Name here") cm.Properties("ServerPassword").SetValue(cm, "Enter your FTP Password here") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above. Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() 'Build a array of all the file names that is going to be FTP'ed (in this case only one file) Dim files(0) As String files(0) = "Drive:\FullPath\YourFileName" 'ftp the file 'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task. ftp.SendFiles(files, "/Enter Your Remote Path", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII ftp.Close() Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try Dts.TaskResult = Dts.Results.Success End Sub End Class

Matthew, this was very very helpful. I could not get the FTP task to delete a remote file for the life of me. I used your script above and just modified the ftp.SendFiles to ftp.DeleteFiles and it worked like a charm. Thanks, S

This is extremely useful. ftp.SendFiles(files, "", True, False) I just used a blank in the remote path, and it works, it does'nt work with any slashes. Thanks so much, this got my code working. There is probably a bug in the FTP componenet in SSIS.

Thanks for your code.. It's very helpful..

Thanks so much for your post! However, I cannot get the modified script below to delete all of the files on the FTP server. I would like to delete all of the files on the server. The file names begin like "romps.dat." and has a final extension like "192". So, an example of a file on the server is "romps.dat.192" If I explicitly state the file name, it works great, but for some reason I cannot get it to delete anything when I insert the wildcard. I've tried it with only one file and with multiple files. 'Connects to the ftp server ftp.Connect() 'Build a array of all the file names that is going to be FTP'ed Dim files(0) As String files(0) = "romps.dat.*" 'ftp the files ftp.DeleteFiles(files) ftp.Close() Thanks in advance for your help!Patrick

Many FTP sites do not support wildcards.

Any suggestions? Thanks, Patrick

Patrick Browder wrote: Any suggestions? Thanks, Patrick Run a directory listing, load that up into an array, and then loop through that array (list, or whatever it's called in .Net world) to delete each file.

That helped. Here's the script if anyone's interested: Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() 'Get file listing Dim fileNames() As String Dim folderNames() As String ftp.GetListing(folderNames, fileNames) 'ftp the files ftp.DeleteFiles(fileNames) ftp.Close() Patrick

Thanks for the code! I noticed however that the example always returns success. To fix this the codeline Dts.TaskResult = Dts.Results.Success should be movedto the last row before the catch.

l ran into an error exception at the ftp.Connect() when executing the code below which is almost exactly the sameMessage = "Exception from HRESULT: 0xC001602A" Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "ftp.(5 char text).com") cm.Properties("ServerUserName").SetValue(cm, "5 char text") cm.Properties("ServerPassword").SetValue(cm, "5 char text") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above. Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() "EXCEPTION OCCUR HERE" 'Build a array of all the file names that is going to be FTP'ed (in this case only one file) Dim files(0) As String files(0) = "c:\tempfolder\1.xls" 'ftp the file 'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task. ftp.SendFiles(files, "ftp.(5 char text).com/tempfolder", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII ftp.Close() Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Trycan anyone give me a hand please. ThxAlex

I ended up using an FTP class library found on the Code Project at - http://www.codeproject.com/cs/internet/ftpdriver1.asp?df=100&forumid=11325&exp=0&select=900150 This library was great for obtaining the directory listing and deleting files from a Unix server (does not work so well on a Windows box). However, the retrieval of files was much slower than the SSIS FTP connection. Please note that you need to create your own solution and add the files from the Code Project website into that solution, You also need to sign your solution, add it to the GAC and copy your solution's DLL to the main C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder in order to reference it inside of the SSIS Script editor.Here is the complete script I am using in case anybody else runs into this issue:Imports Custom.FTP 'This is the Code Project class referenceImports SystemImports System.DataImports System.IOImports System.TextImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain ' This class utilizes the FTP Class Library found by going to: ' http://www.codeproject.com/cs/internet/ftpdriver1.asp?df=100&forumid=11325&exp=0&select=900150 ' in order to retireve a list of file names from a Unix FTP Server ' It then uses an SSIS FTP Connection for faster retrieval of the actual files 'NOTE: The Custom FTP class also successfully deletes files from a Unix server as well Public Sub Main() Dim fileNames() As String Dim ftpConn As FtpConnection Dts.TaskResult = Dts.Results.Success Try 'Setup a custom FTP connection to Unix ftpConn = FtpConnection.Create(Dts.Variables("ServerName").Value.ToString, _ Short.Parse(Dts.Variables("ServerPort").Value.ToString), Console.Out, Console.Out, _ Dts.Variables("FTPUser").Value.ToString, Dts.Variables("FTPPassword").Value.ToString) 'Next we need to setup a directory link to the site Dim directoryList As DirectoryList = New PassiveDirectoryList(ftpConn) 'Now we retrieve the directory listing which comes back in a byte array: Dim sbClientFeedback As New StringBuilder() Dim sbServerFeedback As New StringBuilder() Dim clientOutput As New StringWriter(sbClientFeedback) Dim serverOutput As New StringWriter(sbServerFeedback) Dim rawDirectory() As Byte = directoryList.GetList(Nothing, clientOutput, serverOutput) 'Next we need to convert the binary to ASCII and convert the text into meaningful file nodes: Dim textDirectory As String = System.Text.Encoding.ASCII.GetString(rawDirectory) Dim fileNodes As UnixFileNode() = DirectCast(New UnixFileNode().FromFtpList(textDirectory, ftpConn.CurrentWorkingDirectory), UnixFileNode()) 'Next we grab the relevant names out of the list and add them to a string collection: ReDim fileNames(fileNodes.Length) Dim intFileCounter As Int32 = 1 For Each fileNode As UnixFileNode In fileNodes If fileNode.FullName.IndexOf(".xml.gz.lmp") > 0 Then fileNames(intFileCounter) = fileNode.FullName.Replace(".lmp", "").Replace("/", "") intFileCounter += 1 End If Next 'Next we resize the array: ReDim Preserve fileNames(intFileCounter - 1) Catch ex As Exception Dts.TaskResult = Dts.Results.Failure Finally 'Now we close this connection as it is not the most efficient for retrieving files: ftpConn.Close() End Try If Dts.TaskResult = Dts.Results.Success Then Dim ssisFtp As FtpClientConnection Try 'Now we create an SSIS connection for pulling the files: Dim ssisConn As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password Dim intRetries As Int32 = Int32.Parse(Dts.Variables("FtpRetry").Value.ToString) ssisConn.Properties("ServerName").SetValue(ssisConn, Dts.Variables("ServerName").Value.ToString) ssisConn.Properties("ServerUserName").SetValue(ssisConn, Dts.Variables("FTPUser").Value.ToString) ssisConn.Properties("ServerPassword").SetValue(ssisConn, Dts.Variables("FTPPassword").Value.ToString) ssisConn.Properties("ServerPort").SetValue(ssisConn, Dts.Variables("ServerPort").Value.ToString) 'The 0 setting will make it not timeout ssisConn.Properties("Timeout").SetValue(ssisConn, Dts.Variables("TimeOut").Value.ToString) ssisConn.Properties("ChunkSize").SetValue(ssisConn, Dts.Variables("ChunkSize").Value.ToString) '1000 kb ssisConn.Properties("Retries").SetValue(ssisConn, intRetries.ToString) 'Next we create the FTP Connection ssisFtp = New FtpClientConnection(ssisConn.AcquireConnection(Nothing)) 'And we pass the modified file names in to retrieve the inflated files: ssisFtp.Connect() 'Since the FTP connection seems to timeout with large files, we need to verify that all of the files downloaded Dim requiredFiles() As String = VerifyFiles(fileNames, Dts.Variables("FtpDestination").Value.ToString) Dim intCount As Int32 = 0 While (requiredFiles.Length > 0 OrElse (requiredFiles.Length = 1 AndAlso requiredFiles(0) <> "")) AndAlso intCount <= intRetries ssisFtp.ReceiveFiles(requiredFiles, Dts.Variables("FtpDestination").Value.ToString, True, False) requiredFiles = VerifyFiles(requiredFiles, Dts.Variables("FtpDestination").Value.ToString) intCount += 1 End While If intCount > intRetries AndAlso (requiredFiles.Length > 0 OrElse (requiredFiles.Length = 1 AndAlso requiredFiles(0) <> "")) Then 'We have to try to ask for each file seperately: Dim singleFile(0) As String For Each thisFile As String In requiredFiles singleFile(0) = thisFile ssisFtp.ReceiveFiles(singleFile, Dts.Variables("FtpDestination").Value.ToString, True, False) Next 'We perform one final check and then throw an error or warning: requiredFiles = VerifyFiles(requiredFiles, Dts.Variables("FtpDestination").Value.ToString) If requiredFiles.Length > 0 OrElse (requiredFiles.Length = 1 AndAlso requiredFiles(0) <> "") Then 'In this FTP scenario, the files are all pointers to 0-byte files so I fire a warning: Dts.Events.FireWarning(0, "Unix FTP Task", "The Following Did Not Transfer: " & String.Join(Environment.NewLine, requiredFiles), _ "", 0) End If End If Catch ex As Exception Dts.TaskResult = Dts.Results.Failure Finally ' Finally we close the SSIS FTP Conection ssisFtp.Close() End Try End If End Sub Public Function VerifyFiles(ByVal CurrentList As String(), ByVal TargetDirectory As String) As String() Dim thisDir As New DirectoryInfo(TargetDirectory) Dim sbFiles As New StringBuilder() Dim haveFile As Boolean For Each listItem As String In CurrentList If listItem <> Nothing AndAlso listItem <> "" Then haveFile = False For Each downloadedFile As FileInfo In thisDir.GetFiles() If downloadedFile.Name = listItem Then haveFile = True Exit For End If Next If Not haveFile Then sbFiles.Append(listItem + "|") End If End If Next Dim outputFiles() As String If sbFiles.Length > 0 Then outputFiles = sbFiles.ToString().Substring(0, sbFiles.Length - 1).Split("|".ToCharArray()) Else ReDim outputFiles(1) outputFiles(1) = "" End If Return outputFiles End FunctionEnd Class

Thanks for the code. I'm trying to receive files from an ftp server. so I changed the code to receive files and it doesn't download anything. Am I missing something? files(0) = filepath and name ftp.ReceiveFiles(files, LocPath, True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII ftp.Close()

Sorry for the delay in getting back to you - notifications are not working for me. If you are connecting to a standard FTP site then use the FTP Task - this stuff really only works for non-Windows servers.

Does anybody know howto modify this code to use NVS DnsNameing convention when Ftping to a mainframe. My from file is Named C:\file1.txt my to file needs to be 'xxxx.xx.xxxx.CREATE.REQUEST(+1)'

This looks like just what I need. Could you please tell me theeasiest way to execute the script within and SSIS dtsx package? Would I need to create a full VS .NET project, or can i just paste this scriptinto a (filename).cmd file and add to the command files properties of the package? Thanks, Paul

I modified this to recieve a file from a mainframe to a directory on my PC. It looks as if it's successful, but it does not download the file. These were my modifications: files(0) = "'xxx.xxx.xxx.xxxx(0)'" 'ftp the file ftp.ReceiveFiles(files, "C:\test\xxx.zip",True, False) I've tried the files(0) = to both with single quotes in the double quotes: "'xxx.xxx.xxx.xxxx(0)'" and without the single quotes: "xxx.xxx.xxx.xxxx(0)". In both cases it shows as if it executed successfully, but it doesn't do a thing. The file is there and I can download it using a bat program or directly through FTP. Any suggestions would be much appreciated. Thanks

Viwaltzer wrote: I modified this to recieve a file from a mainframe to a directory on my PC. It looks as if it's successful, but it does not download the file. These were my modifications: files(0) = "'xxx.xxx.xxx.xxxx(0)'" 'ftp the file ftp.ReceiveFiles(files, "C:\test\xxx.zip",True, False) I've tried the files(0) = to both with single quotes in the double quotes: "'xxx.xxx.xxx.xxxx(0)'" and without the single quotes: "xxx.xxx.xxx.xxxx(0)". In both cases it shows as if it executed successfully, but it doesn't do a thing. The file is there and I can download it using a bat program or directly through FTP. Any suggestions would be much appreciated. Thanks it might behoove you to use the execute process task for ftp'ing. hth

Thanks! Works great :-)

Hi, Is anyone still following this thread? I have a similar issue. The FTP Task in SSIS 2005 can connect to an AS/400 FTP Server, and it can list the files needed, but the file names are cryptic, and fail. Here is the listing: FC003 3166662 10/31/08 19:46:53 *STMF BSWAF20081030.TXT FC003 3256877 10/30/08 17:42:52 *STMF BSWAF20081029.TXT FC003 3165556 10/29/08 19:43:53 *STMF BSWAF20081028.TXT ... When I put the whole "filename" as shown above, I get a 550 error - Specified directory does not exist. When Ionlykey the BSWAF20081030.TXT file name, I get the same error. I am able to FTP GET this file from the FTP Client that comes with Windows XP. Thoughts? Bob

Hello, hello, Steve B.? Are you there? Can you help? It appears that IBM and Microsoft are not talking about this issue between the IBM FTP Server and the SSIS FTP Client. They don't like each other I guess. That leaves poor little developers like me out in the cold. See the prior question. I work for an important Microsoft Client, and I am looking bad because I cannot get the FTP connectors to work properly for the AS/400 FTP Server. Anyone? Bobby T

Bobby T. wrote: Hello, hello, Steve B.? Are you there? Can you help? It appears that IBM and Microsoft are not talking about this issue between the IBM FTP Server and the SSIS FTP Client. They don't like each other I guess. That leaves poor little developers like me out in the cold. See the prior question. I work for an important Microsoft Client, and I am looking bad because I cannot get the FTP connectors to work properly for the AS/400 FTP Server. Anyone? Bobby T if you can ftp to AS/400 using a third-party console application, then you can use the execute process task to do it for you. hth

Have you tried SSIS+ library?

I created two tasks to ftp the same file. One using the code sample posted here and the other using an FTP task. The code sample ran 5 times faster than the FTP task. No idea why, but needless to say I'll be coding the FTP process from now on.

Hi Matthew, Thanks for sharing the code snippet. I was looking for a solution to avoid a CWD/CD command that is inherent in the FTP Task and your script did the trick. Is there a way to get file listing from FTP sites based on a pattern ("*2008.DAT")? The listing method pulls back an array of folders and files but does not let the user to specify any options. Thanks again, V

You legend - I've been looking for 2 days to see if there's a way I could replicate the ftp -s command (where I can pass a script into the ftp command). This is going to save me a LOT of time. Thanks niall

hi all,.the said code was helpfull. as i am novice so it was v.helpfull 2me.My needs are..I have to load the bak files to the ftp server. the file name should be in format of backup_[YYYY][MM][DD][HH][MM] YYYY Four digit year MM- 2 digit Month DD 2 digit Date HH Constant at 23 - This is because the backup happens every night at 23:30 MM Constant at 30 This is because the backup happens every night at 23:30their would be multiple file in folder. and the folder location and credential will comes from db.tasks.1. check the file name and its validation2.check most updated files in local directory (these most-updated files will load to the ftp server)3. If Updated files are not available then Setp 1 will run again after every 30 min 4. If file are available then these file should be removed from the local directory, after uploadingReply me asap please.

Alex, Did you every find the cause to the exception during the .Connect call? I have the same problem.

Hi We've got an SSIS task that FTPs up to a windows server (not sure if its 2005 or 2008, but I suspect the latter). We're using basically the same script as the first post in this thread, but when we call the SendFiles method we get error code0xC001602A with no message. At first we obviously assumed that the whole send was failing but it looks like the actual action is succceeding, but it is still reporting an error? Any help much appreciated Many Thanks Mark Middlemist

Hi Again Just a little follow-up We haven't as yet got this fixed but the one thing I should add is that it is actually carrying out the action, whether it be creating/overwriting or deleting a file, but it is still reporting the error. Any thoughts anyone? Many thanks in advance for any help Mark Middlemist

Hello Patrick,I am having real trouble with deleting files within a FTP server....I used the above code snippet from Mathew to delete files and it worked absolutely fine and then i stumbled across another problm of deleting files having date extn in it. I reckon script task doesn't quite support wildcards and hence cant use filename*.csv to delete files.I then used ur codeftp.Connect()'Get file listingDim fileNames() As StringDim folderNames() As Stringftp.GetListing(folderNames, fileNames)'ftp the files ftp.DeleteFiles(fileNames)And it deleted all the files from the FTP directory.......is there anyway I can use ur code snippet and delete a file like filename_170609.csv (today's file with today's date extn)...Thanks a bunch ! Sweta

Thanks so much Matthew Qualls . It works great.

Hi Matthew, I tried the above and put messageboxes in the code and it was showing the FTP connection was established but file is not written on Mainfrmae. any clue?Thanks,Srinivas

HI,I have used the same code in script task, tested the connection successfully. Bu when i sned the files, script task remains in execution mode (yellow).i tried remote location with blank also "".Please help me to solve this issue. Public Sub Main() Dim success As Boolean Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "XXXXXXXXXXXXXXXX") cm.Properties("ServerUserName").SetValue(cm, "XXXXXXX") cm.Properties("ServerPassword").SetValue(cm, "XXXXXX") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above. Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() success = ftp.Connect() If (success = True) Then 'Build a array of all the file names that is going to be FTP'ed (in this case only one file) Dim files(0) As String '= "\\S-SQL16\IPM Source Data\DataAcademySource\ADDRESS_ROLES.csv" files(0) = "\\S-SQL16\IPM Source Data\DataAcademySource\ADDRESS_ROLES.csv" 'Dim remotePath As String = "/" 'ftp the file 'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task. ftp.SendFiles(files, "\", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII Dts.TaskResult = Dts.Results.Success Else Dts.Events.FireError(0, "ScripTask", _ "Connection Failed", String.Empty, 0) End If ftp.Close() Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try End Sub End Class

Hi, try this public void Main() { string serverName = Dts.Variables["User::FTPServerName"].Value.ToString(); string remotepath = Dts.Variables["User::FTPDestinationPath"].Value.ToString(); string userID = Dts.Variables["User::FTPUserID"].Value.ToString(); string password = Dts.Variables["User::FTPPassword"].Value.ToString(); string sourcePath = Dts.Variables["User::SourcePath"].Value.ToString(); try { string Result = FtpPut(serverName, userID, password,sourcePath, remotepath); Dts.TaskResult = (int)ScriptResults.Success; } catch(Exception) { Dts.TaskResult = (int)ScriptResults.Failure; } } private string FtpPut(string serverName, string userID, string password, string sourcePath, string destinationPath) { string result = string.Empty; string sourceFile = Path.Combine(sourcePath, "StagingData.txt"); string batchFilePath = Path.Combine(sourcePath, "FTPBatch.txt"); StringBuilder sb = new StringBuilder(); sb.Append("open " + serverName + Environment.NewLine); sb.Append(userID + Environment.NewLine); sb.Append(password + Environment.NewLine); sb.Append("ascii" + Environment.NewLine); sb.Append("quote site conddisp=delete cylinders primary=30 secondary=20 recfm=fb" + Environment.NewLine); sb.Append("quote site lrecl=1000" + Environment.NewLine); sb.Append("put "); sb.Append("\""); sb.Append(sourceFile); sb.Append("\""); sb.Append(" '" + destinationPath + "'" + Environment.NewLine); sb.Append("close" + Environment.NewLine); sb.Append("bye" + Environment.NewLine); string realBatchText = sb.ToString(); //create the batch file. byte[] realBatchTextBytes = new UTF8Encoding(false, true).GetBytes(realBatchText); using (Stream writer = new FileStream(batchFilePath, FileMode.Create, FileAccess.Write, FileShare.None)) { writer.Write(realBatchTextBytes, 0, realBatchTextBytes.Length); } //Process Info to FTP and run Batch File created ProcessStartInfo startInfo = new ProcessStartInfo(); startInfo.Arguments = string.Format("-s:\"{0}\"", batchFilePath); startInfo.FileName = "ftp.exe"; startInfo.CreateNoWindow = true; startInfo.UseShellExecute = false; startInfo.WindowStyle = ProcessWindowStyle.Hidden; startInfo.RedirectStandardOutput = true; //Start Process using (Process process = new Process()) { process.StartInfo = startInfo; process.Start(); result = process.StandardOutput.ReadToEnd().ToString(); process.Close(); } File.Delete(batchFilePath); return result; } } }

Hello,I am using SQl server 2005, i can use only VB.NET

This is the function converted from C# to VB.net. hope this will be helpful Private Function FtpPut(ByVal serverName As String, ByVal userID As String, ByVal password As String, ByVal sourcePath As String, ByVal destinationPath As String) As String Dim result As String = String.Empty Dim sourceFile As String = Path.Combine(sourcePath, "StagingData.txt") Dim batchFilePath As String = Path.Combine(sourcePath, "FTPBatch.txt") Dim sb As StringBuilder = New StringBuilder() sb.Append("open " + serverName + Environment.NewLine) sb.Append(userID + Environment.NewLine) sb.Append(password + Environment.NewLine) sb.Append("ascii" + Environment.NewLine) sb.Append("quote site conddisp=delete cylinders primary=30 secondary=20 recfm=fb" + Environment.NewLine) sb.Append("quote site lrecl=1000" + Environment.NewLine) sb.Append("put ") sb.Append("""" + sourceFile + """") sb.Append(" '" + destinationPath + "'" + Environment.NewLine) sb.Append("close" + Environment.NewLine) sb.Append("bye" + Environment.NewLine) Dim realBatchText As String = sb.ToString() 'create the batch file. Dim realBatchTextBytes As Byte() = New UTF8Encoding(False, True).GetBytes(realBatchText) Using writer As Stream = New FileStream(batchFilePath, FileMode.Create, FileAccess.Write, FileShare.None) writer.Write(realBatchTextBytes, 0, realBatchTextBytes.Length) End Using 'Process Info to FTP and run Batch File created Dim process As New Process With process With .StartInfo .FileName = "ftp.exe" .Arguments = String.Format("-s:\"" + batchFilePath + """) .CreateNoWindow = True .UseShellExecute = False .WindowStyle = ProcessWindowStyle.Hidden .RedirectStandardOutput = True End With .Start() result = .StandardOutput.ReadToEnd().ToString() .Close() End With File.Delete(batchFilePath) Return result End Function Thanks,Srinivas

You could use the SSIS FTP Task++ from eaSkills. It has wildcard support and SFTP capabilities and it's free. http://www.easkills.com/ssis/ftptask

Try the SSIS FTP Task++ from eaSkills. It works for me using a Unix FTP Server and it's free. http://www.easkills.com/ssis/ftptask

hello Mark, I just had the same as you had.... still working on solving the problem... I suspect that has something to do with VISTA.... my SSIS FTP package was working good in XP... I just have my OS changed to VISTA last week and noticed this problem today...Did you find out any thing with your problem ? Thankskythanh

whn i use this code its getting an error saying "dts is not declared"

Hi, I believe there may be a bug in the ftp.SendFiles command. In my case, if the file already exists on the server, the command throws error "System.Runtime.InteropServices.COMException (0xC001602A): Exception from HRESULT: 0xC001602A" This happens even though I have the overwrite property set to True. The only workaround I've been able to find for this is to first delete the file using ftp.DeleteFiles, then call ftp.SendFiles. I'm running SQL Server 2008 (10.0.2531) on Windows Server 2008 R2 and my SSIS package is coded in Visual Studio 2008 R2. Anyone else have this issue and come up with a way to get ftp.SendFiles to overwrite?

It took awhile, but I found the real bug (or I could be doing something wrong). In my Script Task, if all I do is execute the command ftp.SendFiles it will send the files and overwrite them if they exist - all good there. However, in my case I want to immediately confirm the file is actually on the FTP server - I don't want to trust that the file is there soley on the fact that ftp.SendFiles didn't throw an exception, so I call ftp.GetListing and loop over the results to ensure the file I just sent exists on the FTP server, so far so good. If this is the first time the file is being transferred and thus you are not overwriting anything this will work, however, if you are overwriting a file it will fail consistently. The only way I've been able to get this to work is to first copy all the files to the FTP server, disconnect from the FTP server using ftp.Close, reconnect using ftp.Connect and then call ftp.GetListing. If anyone is interested, I can post code samples which will reproduce this bug on your machine.

Hi Matheww, Thank you for the code.I am a newbie to SSIS. It was very helpful. The script task succeeded, however it did not FTP the file to the server. Need help with this. Thanks, Nivi.

Hi, those who are facing 0xC001602A exception, try setting the UsePassiveMode to true. cm.Properties("UsePassiveMode").SetValue(cm, true) Thanks, RencyRency

Hi, I am a newbie to SSIS and been looking for awhile whether it is possible to write a dynamic script (SQL sp or VB or whatever) that dynamically passes different FTP related parameters (FTP server, login, password, filepath, file, etc...) and FTPs/uploads files to different FTP servers. Basically, I am trying to create a SSIS package that will reference a SQL table for various input parameters (i.e. file name, FTP server where to send the file, user login/password for that particular FTP server, etc...) Not sure if this is clear what I am asking, but using FTP task in SSIS does not allow this - it seems that you can only specify one FTP server and related credentials and cannot vary this dynamically at package's run time. Thanks,

Hi Rossoneri76, If you are going to use the FTP task built in to SSIS, You can store certain settings within SQL, FTP server, Login etc the only thing you can't do is the FTP password as it needs to be manually added within the package. It can be done with script though. If you store the settings within SQL, use a Execute SQL to get the values and store each one of them in a seperate variable.. then within the FTP connection manager, go to expressions, select the relevant one and but in the relevant variable. Therefore when the package runs it picks up the settings from sql assigns them to a variable and then the ftp connection manager picks up the required settings. Hope that helps.

How do I pass three files all at once? Below did not work for me. It only passed the first file. Dim files(0) As String files(0) = "Drive:\FullPath\FileName1" & "Drive:\FullPath\FileName2" & "Drive:\FullPath\FileName3" ftp.SendFiles(files, "/Enter Your Remote Path", True, False)

Thanks - I do this script but receive files (ftp.ReceiveFiles). The files are saved to the D: drive yet once received the C: drive space is eaten up. It'll eventually release it but I can't figure out how to force it - I assume it's a cache thing? Issue being I send over several large files so the C: drive goes from over 1GB to about 50MB as the files get transferred. Sometimes in the middle of it a chunk of drive space is reclaimed but usually it gets eaten up over the FTP process and then later (sometimes minutes, sometimes days) the drive space will automagically free up. Definitely related to this process - I can click the SSIS with this script task and see the drive space disappear as it runs. I can't determine how to force the cache to empty?

Hi Matthew, I am trying to use your script and I get an error and I don't know what I am missing. Error 2 Name 'Dts' is not declared. C:\Users\XXXXXX\AppData\Local\Temp\13\SSIS\ST_fc630fdb35fe4f6ab3d11eba5905f5e5\ScriptMain.vb 64 13 ST_fc630fdb35fe4f6ab3d11eba5905f5e5 Am I missing a reference?? I will appreciate your help. Thanks,

I see why you build a client connection from code. It seems hard or undoable to add an FTP task from the toolbox and program it's properties in a loop.

Hi Matthew, How do you put your code in SSIS? I'd like move my output files in batch to FTP. Please help me. Thank you, Thomas

Thanks for your suggesttion, it worked when remotedirectory is ""

Thank You Mathew you have saved me from hours of painful gui work. Your code is awesome the one thing I noticed and would suggest to those of you encountering no file after a successfull run is did you have the correct \ at the end of the recieve file location. I took me a few minutes to realize that I was not ending my path with a \. My question is this how can you force this to fail in an SSIS package within a scheduled job on SQL 2005. If i am locked out of the account it still shows success even though no connection or file was made. it does display the below error in bids if I am debugging but settin the package to fail on error does not seem to achnowledge the error. (see below) Error: 0xC001602A at ssis_fts_corp_prbz_net_ES1, Connection manager "{6449CE4A-6536-432E-945A-1FA79480F2A6}": An error occurred in the requested FTP operation. Detailed error description: The password was not allowed thanks in advance for your time. -Jbird

Thanks Matthew. That was very helpful. I have run this in C# for SSIS 2008 R2 and it works. Below is my code sample based off your VB version. Included is a check to only download files which do not exist on the local directory. public void Main() { string[] ftp_fileList; string[] ftp_folderList; string[] local_fileList; string[] getFiles; string serverName = Dts.Variables["User::serverName"].Value.ToString(); string serverUserName = Dts.Variables["User::serverpUserName"].Value.ToString(); string serverPassword = Dts.Variables["User::serverPassword"].Value.ToString(); string localFolder = Dts.Variables["User::localFolder"].Value.ToString(); ConnectionManager cm = Dts.Connections.Add("FTP"); cm.Properties["ServerName"].SetValue(cm, serverName); cm.Properties["ServerUserName"].SetValue(cm, serverUserName); cm.Properties["ServerPassword"].SetValue(cm, serverPassword); cm.Properties["ServerPort"].SetValue(cm, "21"); cm.Properties["Timeout"].SetValue(cm, "0"); cm.Properties["ChunkSize"].SetValue(cm, "1000"); cm.Properties["Retries"].SetValue(cm, "1"); FtpClientConnection ftp = new FtpClientConnection(cm.AcquireConnection(null)); ftp.Connect(); //Get a list of all available files on the ftp server. ftp.GetListing(out ftp_folderList, out ftp_fileList); //Get a list of all existing files on the local drive. local_fileList = Directory.GetFiles(localFolder); //Build a list of files which need to be downloaded. StringBuilder getList = new StringBuilder(); foreach (string ftpFile in ftp_fileList) { string comp = localFolder + ftpFile; if (Array.IndexOf(local_fileList, comp) == -1) { getList.Append(ftpFile); getList.Append("|"); } } if (getList.Length > 0) { getList.Remove(getList.Length - 1, 1); getFiles = getList.ToString().Split('|'); ftp.ReceiveFiles(getFiles, localFolder, true, false); } ftp.Close(); Dts.TaskResult = (int)ScriptResults.Success; }

Hi there, Wondering if anyone has had to do something like this before: I have to export some files to an external FTP site outside our domain. Initially I could not connect to it using something like FileZilla so contacted our IT team about it. Apparently we have to specify a proxy server first and then connect to the FTP site. Using a tool like FileZilla I can specify a generic proxy of "HTTP/1.1 using CONNECT method", then enter in the proxy host (proxy.domain.local), a port and my domain user name and password. So then when I connect to the ftp site (ftp.domain.com) and specify a user name and password it works. How do you do this using an SSIS Script Task??? I can use a SSIS Script task to FTP files to other FTP sites that don't need to go through the proxy server but I just can't do it to external sites that require authentication from the proxy server. I've searched this forum, seen some connections like ftp_user_name@ftp.domain.com or ftp.domain.com:21.ftp_user_name.ftp_password but they don't work. Any thoughts would be appreciated. Kind regards, Chris

Hi Chris, The standard SSIS FTP Task (and classes) doesn't support advanced features like proxy support. If you can use third-party solutions, check the commercial CozyRoc SFTP Task. It includes support for both SSH and FTPS protocols and supports HTTP, SOCKS4, SOCKS5, FTP Site, FTP Open and FTP User proxy types.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Try Dim dbConn As ConnectionManager = Dts.Connections("connectionstringnamehere") dbConn.ConnectionString = "Data Source=datasource;User ID=userid;Password=password;Initial Catalog=databasename;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;" Dim ftpConn As ConnectionManager = Dts.Connections("ftpconnectionstringname") ftpConn.Properties("ServerName").SetValue(ftpConn, "servername") ftpConn.Properties("ServerPort").SetValue(ftpConn, "21") ftpConn.Properties("ServerUserName").SetValue(ftpConn, "username") ftpConn.Properties("ServerPassword").SetValue(ftpConn, "password") Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try End Sub End Class create connection strings for production server and ftp sertver first and then use this script in script task. srikrishna