Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Tuesday, August 5, 2014 6:05 AM



Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 14,545, Visits: 38,406
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:
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:

mget *.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?


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1599618
Posted Tuesday, August 5, 2014 6:24 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 9,940, Visits: 9,338
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% >> %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