June 8, 2008 at 5:37 pm
I'm trying to use a company that allows me to upload/download files after some processes have been done and am running into issues in relation to FTP.
I've tried using the FTP upload/download controls built into SSIS as well as making a script task that uses the code aspects of this control and keep running into the same problem.
My problem is that many times it seems like I "hang" on either the upload or download, or both. By this I mean, I have a 10 MB file and I see it upload quickly, get processed by my vendor, and it can just sit there for upwards of an hour and acting as if it's still uploading. And the same behaviour happens when downloading.
I've made sure I have all of the service packs, etc and can't find anything else that might help me to isolate / solve my problem, thus I'm looking to see if anyone here in these forums has any ideas of where to push me.
June 10, 2008 at 5:11 am
Wild guess (since you have not gotten any replies from those who know)-- with those symptoms, the first thing I would check is the FTP BYE command in my FTP command script (if one is being used). But it should not be necessary, how do you check for transfer complete in your code?
June 10, 2008 at 5:29 am
There is no need to check for bye, etc.
This is using the following line:
http://ftp.ReceiveFiles(fileNames, "DOWNLOAD_PATH", True, False)
It's all very basic overall and should be handled gracefully by Microsoft, since the vendor is using the FTP Service for MS and I'm using the DTS pieces from MS, you'd think they'd play nicely.
When you send files it's just http://ftp.SendFiles(blahblahblah).
Both of these stick on me and end up not working. And like I've said, I tried it from multiple machines, multiple ISP's, etc.
June 10, 2008 at 5:53 am
OK, another wild guess. It looks like your "file names" is expected to be a string array. Might you have it as a simple string?
June 10, 2008 at 6:08 am
It's not that. You are correct however that filenames is a string array, however my problem isn't transferring the file.
It's that I'll send the file up/down and it will transfer, and then the script taks will just hang for upwards of an hour.
June 10, 2008 at 6:20 am
Hang in there Terry, sooner or later someone who, unlike me, knows what they are talking about will join in.
I'm surprised that the documentation for http://FTP.ReceiveFiles seems to expect the process to "block" until the transfer is done. Is there no async mode with a way to query progress/completion status? Also, another wild guess, is the file actually binary? (Although if it doesn't finish, it does not matter much.)
I have not been able to use the SSIS FTP tools, because of the silly bug that insists the the source path start with a "\" (making access to mainframes impossible.) Instead I use the CMD FTP call, and supply a FTP script file (hence the "BYE" comment.)
You can guess I am just fishing...
June 10, 2008 at 7:43 am
I chose not to go the SSIS FTP gui route for various reasons, number 1 reason being that it simply is not flexible enough.
In my case, I had to log onto the FTP site, get a directory listing, and compare that with a local site, which sometimes had files, sometimes didnt, in either root or archive.
- If not found, the task needed to get the FTP file, and then rename the FTP file to the FTP site's archive destination.
The FTP GUI could not offer this flexibility, so I asked one of my .NET developers to assist, and we wrote the following .NET component, which works, asynchronously, and perfectly in batch
VARIABLES Used
varSourceArchiveFTPPath, varSourceRootFTPPath, varConnectionStringNoPort (to be used by the .NET FTP component)
CONNECTION Managers
SourceFTPConn
DestFTPDest (where you are going to be checking first)
DestArchiveDest (where you are going to be checking second)
SCRIPT
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Imports System.IO
Imports System.Net
Public Class ScriptMain
Public Sub Main()
'1) Use the connections from the connection manager
'a. FTP Connection Manager
Dim FTPConn As ConnectionManager = Dts.Connections("SourceFTPConn")
'b. Local root folder connection (required to get the foldername connection string)
Dim LocalRootFoldercn As ConnectionManager = Dts.Connections("DestFTPDest")
'c. Archive root folder connection
Dim ArchiveRootFoldercn As ConnectionManager = Dts.Connections("DestArchiveDest")
'd. Local root folder connectionstring
Dim LocalRootFolderName As String
LocalRootFolderName = LocalRootFoldercn.ConnectionString
'e. Local Archive folder connectionstring
Dim ArchiveRootFolderName As String
ArchiveRootFolderName = ArchiveRootFoldercn.ConnectionString
'f. Working Directory variable value
Dim WorkingRootDirectory As String
WorkingRootDirectory = CType(Dts.Variables("varSourceRootFTPPath").Value, String)
'g. Archive Directory variable value
Dim WorkingArchiveDirectory As String
WorkingArchiveDirectory = CType(Dts.Variables("varSourceArchiveFTPPath").Value, String)
'h. create the FTP object that sends the files and passes it to the FTPConn
Dim ftp As FtpClientConnection = New FtpClientConnection(FTPConn.AcquireConnection(Nothing))
'i. Get the DWS FTP Connection (without portnumber
Dim ConnectionStringNoPort As String
ConnectionStringNoPort = CType(Dts.Variables("varConnectionStringNoPort").Value, String)
'2) Get all files found on the target ftp path
GetFiles(LocalRootFolderName, ArchiveRootFolderName, WorkingRootDirectory, ftp, _
DWSConnectionStringNoPort, WorkingArchiveDirectory)
End Sub
Public Sub GetFiles(ByVal LocalRootFolderName As String, ByVal ArchiveRootFolderName As String, ByVal WorkingRootDirectory As String, _
ByVal ftp As FtpClientConnection, ByVal DWSConnectionStringNoPort As String, ByVal WorkingArchiveDirectory As String)
Dim folderNames() As String
Dim fileNames() As String
Dim fileName As String
'1) create a new array where the files not found will be placed
Dim FilesToReceive As New System.Collections.ArrayList()
'2) Connect to the ftp server
'3) Set the working(Directory)
http://ftp.SetWorkingDirectory(WorkingRootDirectory)
'4) Get the listing of all files
http://ftp.GetListing(folderNames, fileNames)
'5) Maintain the array of files
For Each fileName In fileNames
'5a) Check if the file resides on either the root destination, or the archive destination
If Not (FileExists(Path.Combine(LocalRootFolderName, fileName))) And _
Not (FileExists(Path.Combine(ArchiveRootFolderName, fileName))) Then
'5b) If it does not exist, add the file to the array
FilesToReceive.Add(fileName)
End If
Next
'6) Check if the array has more than one file to send
If FilesToReceive.Count > 0 Then
http://ftp.ReceiveFiles(DirectCast(FilesToReceive.ToArray(GetType(String)), String()), LocalRootFolderName, True, True)
'ftp.DeleteFiles(DirectCast(FilesToReceive.ToArray(GetType(String)), String()))
End If
'7) Close the FTP Connection
'8) Rename the files to the old folder
RenameFiles(ConnectionStringNoPort, WorkingRootDirectory, WorkingArchiveDirectory, _
DirectCast(FilesToReceive.ToArray(GetType(String)), String()))
'8) If no exception, set the success status
Dts.TaskResult = Dts.Results.Success
End Sub
Public Sub RenameFiles(ByVal ConnectionStringNoPort As String, ByVal WorkingRootDirectory As String, ByVal WorkingArchiveDirectory As String, _
ByVal filestoMove As String())
Dim fileName As String
Dim strReqUriStr As String
Dim renFileName As String
For Each fileName In filestoMove
strReqUriStr = "ftp://" & ConnectionStringNoPort & WorkingRootDirectory & fileName
Dim ftpReq As FtpWebRequest = CType(FtpWebRequest.Create(strReqUriStr), FtpWebRequest)
renFileName = WorkingArchiveDirectory & fileName
ftpReq.Method = WebRequestMethods.Ftp.Rename
ftpReq.RenameTo = renFileName
Dim response As FtpWebResponse = CType(ftpReq.GetResponse(), FtpWebResponse)
response.Close()
Next
End Sub
End Class
June 10, 2008 at 8:19 am
pduplessis,
Yours is slightly more complex than mine, (I think your developer and myself both found/modified the same code snippet for this), but what I'm running into that doesn't work correctly for me and I'm trying to lay the blame on the FTP Service is the http://ftp.ReceiveFiles and the http://ftp.SendFiles portions.
As I said, what happens is that the file will send flawlessly up to the vendor, be processed by the vendor, etc. Yet the script task still acts as if the file is still being uploaded for upwards of an hour (the task stays yellow long, long, long after everything has been uploaded and processed).
June 10, 2008 at 9:39 am
Terry,
I understand that you are saying sendfiles and receivefiles is flawed, but what I am saying is that I was able to use a receivefiles component without it staying yellow forever and a day.
Perhaps there is some kind of strange setting, not sure.
If you are looking to simply receive files, try the following:
a) Use the FTP component on your SSIS palette on your localhost. If this works, then you know that at least your are able to receive files perfectly
b) Migrate to your server where the job will be running.
Either way, if this doesnt work, try and receive ftp from command line.
If it works from command line and not the FTP task, something may be configured slightly incorrectly.
Some links which may prove useful for you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73331
http://forums.databasejournal.com/archive/index.php/t-42543.html
http://onthelearningcurve.blogspot.com/feeds/posts/default
~PD
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply