any reason to avoid asking ssis to extract files from ftp

Question

Post reply

any reason to avoid asking ssis to extract files from ftp

stan

Ten Centuries

Points: 1307
More actions
December 10, 2025 at 2:46 pm

#4698431
hi we have to replace talend which generally was used to move files.
talend's replacement except for a handful of sql sourced etl's is an OO code based "pipeline" architecture. the sql stuff has been converted to ssis.
we have to begin extracting about 6 files daily from an ftp server and throwing them out to a share drive for further consumption by ssis. one of us wants to make this the responsibility of the new "pipeline" architecture and team. another wants to investigate if that stuff can be moved instead by ssis (maybe fabric in the future).
cadence seems a pro for ssis. having yet another dept, support schedule and telephone call in the picture seems a con for the new pipeline. an "established" file movement process is a pro for the pipeline. In the past, flopping between 2 technologies to deal with a problem partly related to talend and partly related to ssis was awkward for a variety of reasons.
are ftp servers difficult to bake into ssis for flat file sources? how does the community feel about this choice in general?
- This topic was modified 2 weeks, 6 days ago by stan.

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

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

Site Owners SSC Guru Points: 80411 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 740318 More actions · Answer 2

There is an FTP task in SSIS (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/ftp-task?view=sql-server-ver17) and it works. Not SFTP though.

Works fine and I ahve friend still running xfers via ftp (or sftp via third party)

Mr. Brian Gale SSC-Insane Points: 24989 More actions · Answer 3

Just to add to Steve Jones answer - If you are on a modern OS, you could use powershell/command prompt as well to call the sftp command if you needed to use sftp to transfer files. Or have a Windows (assuming Windows OS) scheduled task that calls out to the powershell/bat file to run the SFTP commands. I recommend the powershell/bat file for handling the scheduled task part only because it is easier to debug and troubleshoot than calling the sftp command directly. You can also use a script task in SSIS to call C# stuff to do SFTP if SFTP is required. But if you just need vanilla FTP, then use the baked in SSIS FTP connector.

My opinion though is similar to yours - reduce the number of middlemen to be as small as possible. If you can do it all in SSIS, then do it all in SSIS. This makes troubleshooting easier as it is 1 log to review and 1 system to check up on. The more middlemen you have, the easier it is to finger point and blame someone else and never actually solve the problem.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

frederico_fonseca SSCoach Points: 16294 More actions · Answer 4

have a look at this thread - I've posted good info about using the free winscp to do what you need.. from within ssis.

https://www.sqlservercentral.com/forums/topic/ssis-sftp-using-winscp

stan Ten Centuries Points: 1307 More actions · Answer 5

thx steve, brian, frederico. early indications are that this is what im going to have to use. ..

Globalscape EFT Arcus, a cloud-based Managed File Transfer (MFT) service that supports FTP, but also many other secure protocols (FTPS, SFTP, HTTPS) for enterprise file transfers, offering more security and automation than basic FTP.

am i going to wish i hadnt started down this road?

frederico_fonseca SSCoach Points: 16294 More actions · Answer 6

hum... using that with SSIS would be a big no no for me - why use a cloud thing for local processing (SSIS is local to server) - if you eventually go with Fabric, then you have inbuilt FTP/SFTP so using another third party tool is a waste.

unless you use that tool as the Server (to which ALL clients would then connect to send/receive files) I would stay away from it (or any like it - nothing to do with the vender itself, just fact that it is paid, and a overkill for a simple client)

stan Ten Centuries Points: 1307 More actions · Answer 7

thx frederico, for the moment one of our partners puts 6 files there daily... and yes , while the 6 files are a drop in the bucket, all clients send (maybe receive too) via that product already...high volumes of order data....my interest though, until our partner can target a share, is just those 6 files.