Doing a FTP Pull into Sql Server 2008

Question

Doing a FTP Pull into Sql Server 2008

PK-669256

Valued Member

Points: 60
More actions
April 15, 2010 at 1:33 pm

#386714

I need to find a way to import a file into SQL Server 2008 that is accessed via FTP. Any suggestions?
Thanks

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

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

I recommend doing this with a .net application. We have to do this regularly via ftp and http and I set up a table driven .net application that downloads, unzips (if necessary) pre-processes then bulk inserts data into servers at different times.

That way you get the robust error handling, retrying and logging that you really need for a production environment.

The probability of survival is inversely proportional to the angle of arrival.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

I would suggest SSIS.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

SSIS is a natural solution, you might also like to consider:

1. An external tool like WGET (call from T-SQL/Agent then bulk load the local file)

2. SQLCLR

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 4

Is the file name the same every time? Same location? Or do you need to somehow read what's on the FTP site and then programmatically pick a file to retreive?

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 5

You can alternatively use BCP to export the data , Zip it (7za is free and also run from command line)

then using ftp you can copy to destination , unzip it and use bcp in to import.

I have been doing this since past 6 months and transporting around 20 gb of data across wan. The whole process just takes around 1.5 hrs and is fully automated using batch file.

David-Leibowitz SSChasing Mays Points: 610 More actions · Answer 6

SSIS is the way to go. If you need more functionality like sftp, look into CozyRoc.

SQLScrubs: SSRS Audit, Optimization & Management reports

Andreas Beuschel SSC Veteran Points: 293 More actions · Answer 7

I use SSIS and cuteFTP (Pro edition) if I am dealing with protocols like SFTP or HTTPS

John McC Hall of Fame Points: 3410 More actions · Answer 8

I use SSIS and WinSCP.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: