FTP and file transfer from SQL Server

I would like to confirm that it is possible to do the following with SQL Server.

I believe that using DTS from within SQL Server I will be able to:

Schedule a query to run automatically
Export this query to a CSV text file or XML file
FTP this file to another machine

If anybody has some more info on the specifics of doing this I would be grateful.
Who is Participating?
It is possible to FTP transfer file directly in SQL Server. You have to create DTS package using DTS package designer in Enterpise manager. You can also schedule this task to run on a regular basis.
Basicly you have to create an dts package that will run the query and export it in a text file and ftp-transfer it using DTS package designer. After that you can schedule this using SQL Server Agent (on the Jobs section).
Hi Jod,
> Schedule a query to run automatically
 you can do that. Use EM (Enterpise manager) Export wizard.

> Export this query to a CSV text file or XML file
 Use DTS wizard in EM. If you use MS SQL2K you can export it as XML as well. If you use lower version of SQL Server, this is not possible.

> FTP this file to another machine
 You can run shell programs directly from SQL (i think the name of the procedure was xp_cmdshell or something like that). So you can start some command line ftp client to transfer the file.

Hope this helps.
JodAuthor Commented:
I thought SQL Server could FTP the exported file through an internal FTP functionality built into DTS?

Basically I am not totally familiar with SQL Server and the specifics of DTS so just wanted to check whether the above is feasible.
JodAuthor Commented:
That confimrs it.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.