Solved

FTP and file transfer from SQL Server

Posted on 2001-07-30
5
778 Views
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.
0
Comment
Question by:Jod
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:acampoma
Comment Utility
listening
0
 
LVL 3

Expert Comment

by:ibro
Comment Utility
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.
0
 
LVL 5

Author Comment

by:Jod
Comment Utility
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.
0
 
LVL 3

Accepted Solution

by:
ibro earned 100 total points
Comment Utility
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).
0
 
LVL 5

Author Comment

by:Jod
Comment Utility
That confimrs it.

Cheers,
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

10 Experts available now in Live!

Get 1:1 Help Now