Causes for SSIS FTP Task RemotePath and LocalPath Error
Here is an error I saw my first few times working with the SQL Server Integration Services (SSIS) FTP task:
Directory is not specified in the file connection manager "VariableContainingThePath".
I have figured out two different causes for this error from the FTP task. In my direct experience I was using IsLocalPathVariable = True with an SSIS variable mapped to the LocalPath property. But after thinking about it, I imagine both of the following scenarios would be possible even if you were not using a variable (please add a comment if you can confirm this).
The first cause I observed is that the folder specified for the LocalPath does not exist (which I suppose would also be a possible cause even if you weren't using ). It appears that you need to use an upstream task to ensure that the folder you want to download into *exists* first. I tried it first without the upstream step hoping that the FTP task would create the path for me if it didn't exist (or provide the option). Now that I know this is the way it works, it's easy enough to remember to ensure that the path is valid before allowing the FTP task to execute. I found the FileSystem task useful in this regard; it throws a warning if the folder already exists, but this is harmless enough.
The second cause for this error that I observed is that the LocalPath contains a file name. It may seem natural to put the file name in the path since the RemotePath property requires a full path, including the filename. That intuition would be incorrect, however: put the filename in RemotePath, but leave it off for LocalPath. The FTP task will keep the filename the same when it downloads it to LocalPath. You can use a downstream FileSystem task to rename if you need to.
On a related note, there is something odd about the error message above: it refers to "the file connection manager" even though the FTP task is using IsLocalPathVariable = True. Perhaps I remember reading, though, that an FTP task not explicitly mapped to a connection manager creates an implicit connection manager behind the scenes (??).
Comments welcome.
Dan
FTP Neglected in General
Hi Charley,
Thanks for the comments. FTP, like printing, is one of those areas that is consistently neglected by Microsoft (and other language and platform vendor's too, as evidenced by the fact that Rob MacGrogan's Java printing post is consistently one of the most popular on this site). For example, it's amazing to me that the .NET CLR does not contain FTP capabilities. (And while I'm bitching, why not some native ZIP archive support in .NET and/or SSIS?)
I think one thing that happens with FTP libraries in general, including the FTP task in SSIS, and the old one in DTS (which didn't even support uploading) is that the functionality tends to be limited to the crude, decades old FTP commands. What's needed are aggregate operations that do something more sophisticated on my behalf, like your example of looping through files based on a wildcard match.
Thanks again,
Dan
You can download them by
You can download them by useing variable instead connection, variable can be like /folder/*.*, set isRemoteVariable in true.
Thank you so much!!!
Thank you so much!!! Creating a variable with /*.* and setting the IsRemotePathVariable works like a charm!! You've saved me lots of time in investigating (I was trying to use the Expressions RemotePath property and it didn't work).
RE: FTP Path Errors
Dan,
"RemotePath property requires a full path, including the filename"
This was the problem that plagued the old 2000 DTS FTP task and I was hoping that it'd be fixed with SSIS. I don't understand what's so hard about writing an object that looks for *.* or *.xml (whatever) in the ftp directory and downloads it. Let me deal with any errors it throws.
This aside, you'd think you be able to use the For Each Loop container to loop through the files with *.XXX file type in the FTP directory. You can do this for anything with a regular directory structure, but try doing it with a FTP directory and it seems that you're out of luck.