FTP and file transfer from SQL Server

Posted on 2001-07-30
Last Modified: 2008-03-06
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.
Question by:Jod
  • 2
  • 2

Expert Comment

ID: 6335314

Expert Comment

ID: 6335319
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.

Author Comment

ID: 6337066
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.

Accepted Solution

ibro earned 100 total points
ID: 6337219
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).

Author Comment

ID: 6338076
That confimrs it.


Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now