February 23, 2012 at 9:08 am
pmcdonald (2/23/2012)
Is this only available for SQL Server 2012?
No, it is available in earlier versions also.
Raunak J
February 23, 2012 at 9:44 am
CGSJohnson (2/23/2012)
For each iteration of the FEL? Or does it open one connection? Thanks.
It is one open connection for all data exhange(s).
Raunak J
February 23, 2012 at 9:48 am
this is good task ..what we needed for to transfer files remotely..
thanks for nice post
February 23, 2012 at 10:39 am
peter.row (2/23/2012)
Can the task be configured to use SFTP?
A very good solution I've been using for a couple years now is at:
They have a number of usefull task components in their suite including SFTP, PGP encryption, SSH, Zip, and a number of special source, destination, and transformation components as well. It's not free, but If your company needs to automate these kinds of tasks it works very well and easy to work with.
February 23, 2012 at 10:46 am
Great article.
Your kidding though. The FTP task is smart enout to keep the connection open because I might want to send more files in the same SSIS session. Has anyone verified this by wathing the logs on the FTP server?
So can the FTP task retrieve a list of file names foe me from the FTP server? I have a bunch of XML files and they want to drop them onto an FTP server for me to pick up. I also need to delete the file in the FTP server once I have successfully got the file. How is error handling by the way?
ATBCharles Kincaid
February 23, 2012 at 11:56 am
Seriously??? STILL no SFTP support? And what about the ability to read a remote directory to see if a file exists. I bet they didn't add that either. Microsoft, you suck at life.
It would be very nice to have the ability to do that. The way we got around this for an implementation I did a while back is we timestamped every file down to the millisecond that we sent to the FTP site.
I agree with others about the lack of support for SFTP/FTPS and other transfer protocols; however, the best thing to do is to use a script task or write your own SSIS transformation to do what you want to do in cases where you feel the standard out of the box transformations are not adequate. That is the one nice thing about SSIS is that you can customize it to your needs if it doesn't do something you want.
February 23, 2012 at 12:31 pm
No. This can be also done in SSIS 2005 & 2008. SFTP missing . . .that seems like an issue for anyone running a business.
February 23, 2012 at 12:37 pm
Doh! Didn't see all the subsequent posts already answering. Sorry.
February 23, 2012 at 12:42 pm
At a previous job I had luck with a product called "BizCrypto" when it came to SFTP/FTPS in SSIS. It's not free (ranging from about $3k - >$20k depending on the license you want). It was really good at the job though. This was a few years ago and it had some annoyances - not bugs - more like incomplete features. The devs at Eldos were very responsive though. At one point I felt like I personally was pushing their development cycle. I'd report something and it would show up in a new version 2-3 days later. Eventually the product had everything that I needed to communicated via SFTP and FTPS. I could get remote directory listings, parse them, decide which ones I wanted to retrieve or which local file I needed to send and then send or receive as needed.
I've also done the scripting route as someone else described but I didn't like the idea of having to create batch files that had to be cleaned up afterwards (what if something broke and my batch file was left on a drive with my credentials for a remote server in plain text?). Sometimes you do what you have to do within the constraints of a budget - but it doesn't take a whole lot of time paying for a DBA or SQL dev to justify dropping a couple thousand on a good software solution.
February 23, 2012 at 1:42 pm
We also use CozyRoc for SFTP, Zip, PGP and Excel connections/components. The pricing is good at $400 per year or $1600 for perpetual licensing. On the SFTP task you can receive a remote file list to loop through and download files based on pattern matching. The support is very responsive, usually just a day turn around on support requests. We've been with them for several years and have a multitude of SFTP tasks running. When we first started with SSIS we couldn't believe that SFTP wasn't included either!
MWise
February 23, 2012 at 2:53 pm
We use psftp or curl .. they are free and does the job well.
Just use the script task in SSIS .. and get the job done 😀
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 24, 2012 at 4:06 am
SQLQuest29 (2/23/2012)
We use psftp or curl .. they are free and does the job well.Just use the script task in SSIS .. and get the job done 😀
Yes, psftp is how I do it now- the issue is that MS is making us have to rely on 3rd party freeware to make SSIS usable in normal, everyday business situations.
Shameful.
July 25, 2013 at 4:16 am
Hi Guys ,
Im looking to upload or send a file from local to ftp site using ssis, but my local folder has nearly 100 files...I need to send last updated file, everyday one file is added to local folder, i need to send that updated file to ftp site everyday not the 100 files from local to ftp.
file names like abc_10/07/2013.text
abc_11/07/2013.text
abc_25/07/2013.text
i need to send only latest file to ftp site everyday not all of them ??? im new bie could anyone help me to sort this issue???
manyu thanks in advance
July 25, 2013 at 9:02 am
raju400 (7/25/2013)
Hi Guys ,...
i need to send only latest file to ftp site everyday not all of them ??? im new bie could anyone help me to sort this issue???
manyu thanks in advance
You have come across one of the examples of where SQL Server (and its tool set) are likely not the best thing to use. This sounds like what you need is an FTP sync utility.
The site will know what has already been uploaded as those files will still be there. The sync utility will then upload the new file.
Alternatively is to drop your files into a certain folder, write a command file (batch file) to FTP up the new file (or files), and finally MOVE (not copy) the new files into the final folder.
We do this kind of thing all the time. We have incoming transaction files that get processed and loaded into SQL, then moved to archive. We also do the same with outgoing transactions. For one client its many hundreds of files every day.
ATBCharles Kincaid
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply