Retrive File Information from FTP Site with MSSQL

I have been able to use the query below to get the file structure of a drive on my PC but I need to be able to get this from an FTP Server.  I will be using this to determine specific actions based on the files being added to this directory.   I don't need the files i just need to see the data that i get in the first query shown.

This Works ....

CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir /OD C:\'
DECLARE @Command varchar(1024) =  @Path+'*.*'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL
SELECT * FROM   #DirectoryList
DROP TABLE #DirectoryList

Need this to work:

CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir /OD'
DECLARE @Command varchar(1024) =  @Path+'*.*'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL
SELECT * FROM   #DirectoryList
DROP TABLE #DirectoryList
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The FTP standard does not specify a format for directory listings.  I bring this up because it appears you expect the listing to be in DOS format.  If you already know that this particular FTP server returns listings in a particular format thats great but if not you should be prepared to write logic to parse whatever format the server actually sends.

Anyway, you can't do exactly what you want to do with a simple DIR type command.  There are several steps involved to dealing with the remote FTP server.  You've got to log in, change to the export sub folder, request a directory listing, and then save it to a file.  There is freeware available that does this sort of stuff under the hood and then presents an interface like a mapped network drive but I can't recommend it for a production environment.  Your mileage my vary.

I would use an FTP script to do this work and leave a text file containing the directory listings.  Then process the file like any other text file or maybe even use xp_cmpshell to TYPE it... Im not sure about the length though, is it a small directory listing?

Here is a Robo-FTP script that will do the job plus it has extra logic to prevent accidentally getting a list of any folder other than the export folder.

DELETE "c:\folder\MyDirList.txt"  ;; delete old list file 
FTPLOGON "" /user="UserID" /pw="secret"
FTPCD "export"
FTPLIST "c:\folder\MyDirList.txt"  ;; write new directory list

Open in new window

Jason YousefSr. BI  DeveloperCommented:
You can do it in SSIS using a script task.... let me know if you're interested..
Jason YousefSr. BI  DeveloperCommented:
Here, I've blogged about your're famous now :)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
OK, looks like a good approach by huslayer. And you do have other options.

First problem is you cannot simply do a "DIR" command from the dos prompt for a FTP host.

You really need to use a "FTP" aware or Internet Browser/Explorer aware interface (Internet browsers support FTP interfaces).

Now, we can readily execute external programs - like a DIR command, on in this case a FTP command.

The "standard" FTP program that comes with windows can handle scripting. So, it is possible to build the script, then run the FTP command using the Script.

So, why dont we simply do that ?

The best thing about that approach is it is so very easy to test before you start doing any SQL automation. And you will need a little bit of familiarity with FTP.

So, lets start with a manual FTP session.

1) Open a command window (start, run, cmd)
2) type in FTP and you should get the FTP> prompt and you can type in the following commands
3) OPEN <your ftp site name>
4) USER <username> <password>
5) CD <to the export directory>
6) DIR
7) bye

Now, you dont quite have to follow all of the above, but it is the basic approach. The first deviation we will take from the above is to launch the FTP and OPEN together with some options NOT to autologin

Using huslayers example site above (ta muchly) from the cmd prompt

1) ftp -n
    the above opens a connection to the ftp site without auto logins, but we still need to login....
2) USER test test
    now we should be logged in and ready to issue more commands.
3) MDIR *.* dir.log
    the above command enables a dir to be written to an external file ie dir.log but it also prompts you - so we need to take that into account as well.
4) BYE

Now check the contents of the log file by typing in TYPE dir.log

Not quite the format we are looking for, but easy enough to extract the information.

Having had some success manually (and you might need to vary a little bit for your own FTP site like doing a "CD export" at the FTP prompt before you do the mdir.

We are now ready to script the commands and take care of that prompt. We can take care of the prompt by using the run time switches for FTP. Here we want to use the "-i" switch. Now the commands are easy enough, we have three - the USER, the MDIR and the BYE exactly as we used them above. So, we edit a text file and put those commands in there and save that file. The we tell the FTP command to use the script file we just created.

So, our script file looks like (saved as dir_ftp.scr) :
USER test test 
mdir *.* dir.log

Open in new window

and our ftp command at the dos prompt looks like :
ftp -n -i -s:dir_ftp.scr

Open in new window

So, now we know how we can do it manually, then lets get SQL to do all that for us...

First up, I have created a folder c:\ee as our work folder. Then use SQL to read and write from that folder...

-- first lets create a temporary table to use for writing our script

if object_id('tempdb..my_ftp_script','U') is not null drop table tempdb..my_ftp_script
create table tempdb..my_ftp_script (line varchar(255))

-- now lets write our script into that table

insert my_ftp_script values ('USER test test')              
insert my_ftp_script values ('mdir *.* c:\ee\dir.log')
insert my_ftp_script values ('bye')

-- and export it to a flat text file so we can use it with our FTP command

exec xp_cmdshell 'bcp "select * from tempdb..my_ftp_script" queryout "c:\ee\my_ftp_script.scr" -c -T -CACP'

-- now we have a script, we can now issue our FTP command

exec xp_cmdshell 'ftp -n -i -s:c:\ee\my_ftp_script.scr'

-- and prepare to import the results

if object_id('tempdb..#log') is not null drop table #log
create table #log(my_var varchar(max))       
bulk insert #log from 'c:\ee\dir.log'

-- and finally select / check the contents

select *, substring(my_var,50,255) as [file_name]
from #log

Open in new window

So, the SQL is not too hard, and apologies about the FTP introduction before hand, but you really should be trying it manually before you can automate it, because if it cannot be done manually then it will never be automated :)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.