Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Secure FTP via putty/psftp.exe and wildcards Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 6:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 12,923, Visits: 32,317
I've got a few SSIS packages that copy files from an SFTP server to a local folder, and the SSIS package then processes the files.

I'm currently doing this with batch files and calling psftp. I'm aware of WinSCP and an SSIS add in for SFTP dll i could use as well, but have not dug into them yet.I've also seen an opensource powershell snap in for SFTP as well. switching to anything that works better is fine with me.

my problem seems to lie with psftp , but it may be a windows issue, or it may be a SFTP server(WSFTP Pro) issue.
i need to either remove the files form the main folderon the FTP site, or delete them; either would work fine for my process.

I have a script file with these basic commands:
lcd D:\SSIS\DAILY_DATAWAREHOUSE_FEEDS\Zips\
mget *.zip
mv *.zip ./ARCHIVE

the problem is, whether is use the rm (remove command) or the mv(move command) WITH A WILDCARD, it fails.
if i were to explicitly remove specific files by name, it works:

lcd D:\SSIS\DAILY_DATAWAREHOUSE_FEEDS\Zips\
mget *.zip
rm DailyFeed_20140801.zip
rm DailyFeed_20140802.zip
rm DailyFeed_20140803.zip


so, as i'm trying to bring my command line skills up to strength, im looking to try and use a FOR loop to issue the command for each file i just downloaded to remove or archive them.

my question is any of the following:

have you seent his behavior yourself, and did you resolve it the same way i plan?
if you use WINSCP, do you have the same issue with wildcards?
how do you handle similar issues, where you need to remove multiple files via SFTP?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1599618
Posted Tuesday, August 5, 2014 6:24 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 4,480, Visits: 3,933
I did something almost identical several years ago (over FTP, not SFTP) using batch files to do an mget and then delete them. The problem I saw was that the FTP folder could potentially have a file posted to it after I fire the mget and before I deleted them. Besides, I wanted to be certain that the files arrived intact, so I used a slightly different approach.

I first connected and did my mget. I then disconnected from the FTP server. Now the files are on a hard drive on the server, so I fired the following to dynamically create an FTP script file. The %ftpfile% variable contains the value script.ftp and is the name of the file. The %dirMailbox% variable contains the directory on disk where the files live and the files I received are the only files in that directory when this script runs.

if exist %ftpfile% del %ftpfile%
echo username > %ftpfile%
echo password >> %ftpfile%
echo cd /prod/outbox >> %ftpfile%
echo prompt n >> %ftpfile%
for /f "tokens=*" %%G in ('dir %dirMailbox% /b /a-d') do (
echo rm %%G >> %ftpfile%))
echo quit >> %ftpfile%
start /wait ftp -v -s:%ftpfile% ftp.servername.com >> %logfile%
type nul > %ftpfile%

The result is that you write the rm command for each file you've received. For testing, you can rem out the last two lines to look at the resulting script.ftp file. Is this what you're looking for?



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1599639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse