• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

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.
0
Jod
Asked:
Jod
  • 2
  • 2
1 Solution
 
acampomaCommented:
listening
0
 
ibroCommented:
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
 
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.
0
 
ibroCommented:
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
 
JodAuthor Commented:
That confimrs it.

Cheers,
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now