Jeffrey Williams wrote:
If I couldn't keep every file in a local folder - so that I could use a simple File.Exists() in C# to determine if the file has already been downloaded, then I would pull the full list of files into a lookup component in SSIS. One call to the database to get the full list - one call to the FTP server to get the directory list, then a foreach over the list of files from FTP and a lookup to determine if that file has already been downloaded.
the c# code that is using winscp is doing just that - retrieving the list of files on the remote server - only the files that match a particular pattern in any of the sub folders (which have names that are unknown to the process) - and it is a lot easier and makes more sense to have it on a single block of code.
open sftp session
-- get list of remote files
-- foreach file
---- check if file is new -- against sql server table
------ download if new -- add entry to sql server table
-- close sftp session
lot easier, clearer and maintainable than to have 1 ->script task to get list of files -- 2 -> recorset to hold list of ALL remote files - 3 -> Loop container(over recordset) 4->sql command to check if file exists -5-> script task to download single file - 6->sql command to update table with list of downloaded files