Using FTP Task in DTS SQL 2K

Posted on 2005-05-04
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
    LVL 34

    Accepted Solution

    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

    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

    "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

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    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…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    746 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

    19 Experts available now in Live!

    Get 1:1 Help Now