FTP Task

Question

FTP Task

Briceston

SSCrazy

Points: 2899
More actions
January 15, 2017 at 11:00 am

#401390

Hi,
I have to develop an FTP task that moves files from an FTP server that are older than a month to a department share folder path. What is the best way to accomplish this?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

If the FTP server is in the same domain as the archive server that the department share lives on, then a DOS (ROBOCOPY likely the best) or PowerShell command would do the trick.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Briceston SSCrazy Points: 2899 More actions · Answer 2

Jeff Moden - Sunday, January 15, 2017 11:12 AM
If the FTP server is in the same domain as the archive server that the department share lives on, then a DOS (ROBOCOPY likely the best) or PowerShell command would do the trick.

Thanks for the reply.

The FTP server is not in the same domain.

You mentioned ROBOCOPY, but this task needs to be carried out in SSIS. I actually should of mentioned that in my title description,my bad.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Briceston - Sunday, January 15, 2017 7:14 PM
Jeff Moden - Sunday, January 15, 2017 11:12 AM
If the FTP server is in the same domain as the archive server that the department share lives on, then a DOS (ROBOCOPY likely the best) or PowerShell command would do the trick.
Thanks for the reply.
The FTP server is not in the same domain.
You mentioned ROBOCOPY, but this task needs to be carried out in SSIS. I actually should of mentioned that in my title description,my bad.

I'm with Jeff on this - I'd use DOS to connect to the FTP server and copy the files to the network share. If you also have to delete them from the FTP server, I've found it useful to ensure the files are copied to the network share before deleting them from the FTP server. It prevents the deletion of a file that might not have downloaded correctly. The DOS and FTP scripts should be able to handle everything.

If you need to do it in SSIS, then I'm not the guy to help you.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Briceston SSCrazy Points: 2899 More actions · Answer 4

Ed Wagner - Sunday, January 15, 2017 10:29 PM
Briceston - Sunday, January 15, 2017 7:14 PM
Jeff Moden - Sunday, January 15, 2017 11:12 AM
If the FTP server is in the same domain as the archive server that the department share lives on, then a DOS (ROBOCOPY likely the best) or PowerShell command would do the trick.
Thanks for the reply.
The FTP server is not in the same domain.
You mentioned ROBOCOPY, but this task needs to be carried out in SSIS. I actually should of mentioned that in my title description,my bad.
I'm with Jeff on this - I'd use DOS to connect to the FTP server and copy the files to the network share. If you also have to delete them from the FTP server, I've found it useful to ensure the files are copied to the network share before deleting them from the FTP server. It prevents the deletion of a file that might not have downloaded correctly. The DOS and FTP scripts should be able to handle everything.
If you need to do it in SSIS, then I'm not the guy to help you.

Thanks for chiming in. My FTP download process needs to automated, so SSIS would be the best route for me.

Thom A SSC Guru Points: 99130 More actions · Answer 5

SSIS comes with an FTP task by default, however, you don't have much functionality with it (you can't check all the files on the server and get their age for example). You'll likely need to do this in a couple of steps, if SSIS is the solution you want to take.

Firstly, you'd connect the server via a script task and create a list of all the relevant files. I've found a script someone has written here to connect and get a list, but it'll need a little more work to fit your needs. I don't have any expertise in writing C# though, so I don't know what the code to achieve it would be, but you'd need to adapt it to check the age of the file and not add "new" files to your list.

You'll then need to create a foreach loop container, which firstly downloads the file to your archive location, and secondly deletes the file on the server after it has been downloaded.

Sorry I can't give you much more than that, but that should get you on the right path at least.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Briceston SSCrazy Points: 2899 More actions · Answer 6

Thom A - Monday, January 16, 2017 4:52 AM
SSIS comes with an FTP task by default, however, you don't have much functionality with it (you can't check all the files on the server and get their age for example). You'll likely need to do this in a couple of steps, if SSIS is the solution you want to take.
Firstly, you'd connect the server via a script task and create a list of all the relevant files. I've found a script someone has written here to connect and get a list, but it'll need a little more work to fit your needs. I don't have any expertise in writing C# though, so I what the code you would need, but you'd need to adapt it to check the age of the file and not add "new" files to your list.
You'll then need to create a foreach loop container, which firstly downloads the file to your archive location, and secondly deletes the file on the server after it has been downloaded.
Sorry i can't give you much more than that, but that should get you on the right path at least.

Thank you. This is a good starting point for me. I think the script task would probably be the most challenging part for me since I have never used it.

The "here" link is not working.

Thom A SSC Guru Points: 99130 More actions · Answer 7

Briceston - Tuesday, January 17, 2017 11:06 PM
Thank you. This is a good starting point for me. I think the script task would probably be the most challenging part for me since I have never used it.
The "here" link is not working.

Try it now.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bob Thiele SSC-Addicted Points: 420 More actions · Answer 8

Briceston - Monday, January 16, 2017 4:38 AM
Ed Wagner - Sunday, January 15, 2017 10:29 PM
Briceston - Sunday, January 15, 2017 7:14 PM
Jeff Moden - Sunday, January 15, 2017 11:12 AM
If the FTP server is in the same domain as the archive server that the department share lives on, then a DOS (ROBOCOPY likely the best) or PowerShell command would do the trick.
Thanks for the reply.
The FTP server is not in the same domain.
You mentioned ROBOCOPY, but this task needs to be carried out in SSIS. I actually should of mentioned that in my title description,my bad.
I'm with Jeff on this - I'd use DOS to connect to the FTP server and copy the files to the network share. If you also have to delete them from the FTP server, I've found it useful to ensure the files are copied to the network share before deleting them from the FTP server. It prevents the deletion of a file that might not have downloaded correctly. The DOS and FTP scripts should be able to handle everything.
If you need to do it in SSIS, then I'm not the guy to help you.
Thanks for chiming in. My FTP download process needs to automated, so SSIS would be the best route for me.

We use PowerShell or Batch commands to do our file moves. You set up a task in Windows Scheduler to automate them.