SSIS Script task that FTP's files
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
February 22nd, 2007 6:44pm
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
July 3rd, 2007 7:30pm
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.
July 20th, 2007 1:11pm
Thanks for your code.. It's very helpful..
July 20th, 2007 2:08pm
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
July 31st, 2007 5:49pm
Many FTP sites do not support wildcards.
July 31st, 2007 5:50pm
Any suggestions?
Thanks,
Patrick
July 31st, 2007 5:53pm
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
July 31st, 2007 7:07pm
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.
September 21st, 2007 10:58am
l ran into an error exception at the ftp.Connect() when executing the code below which is almost exactly the same
Message = "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 Try
can anyone give me a hand please. Thx
Alex
September 22nd, 2007 7:33am
First thank you for taking the time to read my post!
I have tried the code you provided here and i keep getting the following error:
[Connection manager "{3246C151-503C-4DE3-AFB6-54FF6D3820A7}"] Error: An error occurred in the requested FTP operation. Detailed error description: The connection with the server was reset .
This error occurs on the GetListing line. I preceded that line with a line to set the working directory and I received no errors.
Do you have any suggestions?
September 23rd, 2007 1:54am
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 reference
Imports System
Imports System.Data
Imports System.IO
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Public 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 Function
End Class
September 23rd, 2007 10:37pm
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()
February 28th, 2008 7:33pm
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.
March 16th, 2008 12:59am
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)'
May 15th, 2008 11:45pm
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
July 31st, 2008 9:42pm
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
August 13th, 2008 2:04am
August 13th, 2008 10:25am
October 23rd, 2008 1:02pm
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
November 3rd, 2008 5:50pm
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
November 15th, 2008 3:49pm
November 15th, 2008 8:23pm
Have you tried SSIS+ library?
November 17th, 2008 4:28pm
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.
December 3rd, 2008 12:29am
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
December 3rd, 2008 10:30pm
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
January 21st, 2009 6:10am
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:30
their would be multiple file in folder. and the folder location and credential will comes from db.
tasks.
1. check the file name and its validation
2.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 uploading
Reply me asap
please.
February 11th, 2009 8:37am
Alex,
Did you every find the cause to the exception during the .Connect call? I have the same problem.
February 25th, 2009 6:36pm
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
April 27th, 2009 11:39am
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
May 11th, 2009 11:06am
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 code
ftp.Connect()
'Get file listing
Dim fileNames() As String
Dim folderNames() As String
ftp.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)...
June 17th, 2009 7:37pm
Thanks so much
Matthew Qualls . It works great.
- Edited by
DayHappy
Monday, July 20, 2009 7:03 PM
July 20th, 2009 10:01pm
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
February 12th, 2010 6:43pm
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
February 26th, 2010 4:30pm
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;
}
}
}
February 26th, 2010 8:03pm
Hello,
I am using SQl server 2005, i can use only VB.NET
February 26th, 2010 8:21pm
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
February 26th, 2010 9:13pm
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 ?
Thanks
kythanh
April 16th, 2010 5:53am
whn i use this code its getting an error saying "dts is not declared"
April 22nd, 2010 7:08pm
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?
July 8th, 2010 12:36am
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.
July 8th, 2010 4:12pm
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.
August 4th, 2010 11:19pm
Hi,
those who are facing 0xC001602A exception, try setting the UsePassiveMode to
true.
cm.Properties("UsePassiveMode").SetValue(cm,
true)
Thanks,
Rency
August 17th, 2010 10:19am
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,
September 24th, 2010 11:08pm
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.
September 30th, 2010 6:12pm
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)
October 27th, 2010 8:35pm
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?
November 4th, 2010 9:33pm
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,
December 21st, 2010 9:36am
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.
March 10th, 2011 6:55pm
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
May 17th, 2011 9:59pm
Thanks for your suggesttion, it worked when remotedirectory is ""
June 1st, 2011 8:27pm
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
September 29th, 2011 3:22am
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;
}
February 7th, 2012 10:40pm
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
February 14th, 2012 2:41am
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.
February 14th, 2012 4:17am
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
February 14th, 2012 5:45am
I have been working on scheduling an SSIS package to ftp files for a couple of days. This worked flawlessly.
Thank you so much for your time and effort putting this script task together.
You the MAN!
August 10th, 2012 8:36pm
Can you please tell me how you would set this up for SFTP instead of FTP?
Thanks to you , the FTP script works, but now I need to create a package sending files to an SFTP site.
August 11th, 2012 12:41am
i changed the TennesseePaul's C# code to VB code....and added some extra code in it works perfect
Thanks Ton saved my time....
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Class ScriptMain
Public Sub Main()
Try
Dim ftp_fileList As String()
Dim ftp_folderList As String()
Dim local_fileList As String()
Dim getFiles As String()
Dim LocalPaths As String = Dts.Variables("User::LocalPaths").Value.ToString()
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "xxxxxx")
cm.Properties("ServerUserName").SetValue(cm,"xxx")
cm.Properties("ServerPassword").SetValue(cm, "xxx")
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))
ftp.Connect()
ftp.SetWorkingDirectory(Dts.Variables("FtpPaths").Value.ToString())
'Get a list of all available files on the ftp server.
ftp.GetListing(ftp_folderList, ftp_fileList)
'Get a list of all existing files on the local drive.
local_fileList = Directory.GetFiles(LocalPaths)
'Build a list of files which need to be downloaded.
Dim getList As New Text.StringBuilder()
For Each ftpFile As String In ftp_fileList
Dim comp As String = LocalPaths & ftpFile
If Array.IndexOf(local_fileList, comp) = -1 Then
getList.Append(ftpFile)
getList.Append("|")
End If
Next
If getList.Length > 0 Then
getList.Remove(getList.Length - 1, 1)
getFiles = getList.ToString().Split("|"c)
ftp.ReceiveFiles(getFiles, LocalPaths, True, False)
End If
ftp.Close()
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Fire error and set result to failure
Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
End Class
August 23rd, 2012 4:13pm
hi ,Rency Fernandes
your code :
cm.Properties("UsePassiveMode").SetValue(cm, true)
it does not working.
it almost throw exception
RESULT:0xC001602A,{A754A946-7A38-4022-A8B9-31CE3C4E82CB},,.... The operation timed out
but thank for all the same.
- Edited by
Monday, December 03, 2012 3:31 AM
additional information
December 3rd, 2012 6:27am
The time out typically is not code related, it is your environment, also consider posting your issue using your own thread.
December 3rd, 2012 5:32pm
Consider posting your issue into your own (new) thread.
I can assume here the values do not get passed as you expect them because it does not connect doing this two different ways.
January 23rd, 2013 9:22pm
I was going to create my own thread, I was just following the forum rules for posts. I will move this to another thread.
January 23rd, 2013 9:26pm
Hello,
Really its very helpful. I need to exactly as it is... but unfortunately I am getting error.
On FTP my files are on particular folder like Data\SQL\SystemInfo\20131016
I use that command in for ftp folder. After I changed and retried it but no luck.. I am getting the error
=====================================================================
SSIS package "Package.dtsx" starting.
Error: 0x1 at Downloading FTP files from Server-1: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection.
This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not
there.
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ST_8fa805fef1694c048276ef300443f903.csproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Downloading FTP files from Server-1
SSIS package "Package.dtsx" finished: Success.
================================================================================
I am not susre what is the wrong I am doing.
October 17th, 2013 3:21am
Hello Santhosh,
Can you help me in fixing the script in my environment. I need to use the same one you did but getting errors. I am using ipaddress in place of servername.
================================================================
SSIS package "Package.dtsx" starting.
Error: 0x0 at VB_FTP, FTP Script Task: Error: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
Error: 0x8 at VB_FTP: The script returned a failure result.
Task failed: VB_FTP
SSIS package "Package.dtsx" finished: Success.
=====================================================================================
October 17th, 2013 3:23am