[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Using FTP Task in DTS SQL 2K

Posted on 2005-05-04
Medium Priority
Last Modified: 2012-08-13
Hi all.  I have a question.  I am looking into a Secure FTP Server to place on a new Web Server we are putting a firewall in place etc.  I need to complete a couple of tasks such as transferring PDF Files over to a directory which is where I started looking into using a Globalscape Secure FTP Server with 128 bit and sending files over. The 2nd thing I need to accomplish is getting approx 5 tables over from our internal production database to the Web Server box Push Only.  I want to push the data every 2 or so hours.  I know the files I can accomplish this using an FTP Client and pushing the PDF files that way.  Then I was going to look into opening post 1433 only allowing outbound from the internal production system.  Is there a way though to also setup with the File Transfer Protocol task pushing this data out to the Web Server as well and somehow getting it imported in once the push is completed to that Web Server?  Just curious.  Thanks all
Question by:sbornstein2
  • 2
  • 2
LVL 34

Accepted Solution

arbert earned 200 total points
ID: 13933242
The "File Transfer Protocol task " doesn't support secure ftp--do you have another client you're going to use?

"The 2nd thing I need to accomplish is getting approx 5 tables over from our internal production database to the Web Server box Push Only."

If you only want to use FTP, use BCP to BCP the data out to text files, FTP them to the outside server and then run a BCP job on that server to reimport.....



Author Comment

ID: 13936043
Thanks Brett.  So I can create a BCP file or something and then after it completes call a job on the outside server to BCP in?  I am just thinking if I need to FTP PDF files anyway then why not also handle the main transfer of data as well if possible.  I never ran a BCP job before is it easy to do?
LVL 34

Expert Comment

ID: 13936316
"I never ran a BCP job before is it easy to do?"

Ya, look in SQL Books online for BCP.  It will by far be your fastest method....

What I would do is come up with a schedule to export the data from the internal site, and then schedule a job on the external site to automatically pick the file(s) up at a certain time---that way you never have to open any additional ports to the SQL Server (to kick off a remote job).


Author Comment

ID: 13937064
Great info Brett.  I have been pondering how to accomplish this for a few weeks now.  The only thing is I will probably have to make sure I know how long the job takes to be able to run a job for that file.  Thanks alot

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

872 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