DTS to ftp files from server to local computer using CuteFTP

Posted on 2005-04-19
Last Modified: 2010-03-19
Hi Experts,

I've been asked to develop a DTS which will pull files from an ftp server and place them on a local computer using CuteFTP.
I'll then need to check for and log errors to a txt file which will be sent by email.  

I'm new to DTS and unsure how to get the FTP side of this to work.  Any idea?

Question by:sarahth
    LVL 5

    Assisted Solution

    I don't think that could do it with DTS.
    The way that DTS works, is that you define a DTS (Data Transformation Service) package, and it transfers objects from one ODBC data source to the other. You can transfer fixed files (i.e., file name is fixed in each DTS package, since a single file can be an ODBC source), but if you want to move several files (which is probably what you need) you could not use DTS package. Also - building DTS packages cannot be done programatically in T-SQL (and I don't think it can be done otherwise, programateically, that is).

    The good new are:
    The DTS is based on SQL-DMO object (Data Manipulation Objects).
    This is how SQL-Server GUIs build and execute the DTS package.
    Please refer to SQL DMO for a complete list of available objects and methods

    Some good links here would be

    and there are MANY MANY others.

    Hope this helps.


    Author Comment

    Someone suggested I use a Dynamic Properties task to set variables which would then be used by a VB script which would invoke the FTP procedure; connecting to the site, downloading the files and then logging errors if they occur.

    Can this be done?
    LVL 30

    Expert Comment

    Does CuteFTP work with the command line?

    Then you could use an Executre Process task to run it.

    Failing that I know the standard windows FTP works with the command line. Also there is an actual FTP task in DTS that you can use.

    If you wanted to just copy several files you could use ActiveX or Execute SQL tasks in DTS.


    Author Comment

    I'll be using CuteFTP Pro which can be run from the command line and is scriptable.  Unfortunetly I have to use this program because the process will involve the exchange of digitial certificates.

    Do you know of any web sites or have any example scripts that will execute the ftp program and retrieve the files?

    LVL 30

    Accepted Solution

    This site

    says that you can write a macro within CuteFTP that can be run from the command line.

    Sorry I don't have any examples but surely CuteFTP help would have examples of using it from the command line?

    If not, usually you can just open a dos box, go to the install path, search for a likely EXE file and run it with a /? switch. In most cases that will give you help.

    So you need to build your command line string from there, then just pop it into a DTS Execute Process Task.

    If you need to do a couple of things, pop your command lines into a .CMD file, and run that file from the command line.

    If you have to do any funky dynamic parameters into FTP, you can build a CMD file on the fly and then run that.

    LVL 30

    Expert Comment


    Author Comment

    Thanks for all the suggestions - the sites recommended were quite helpful so I think I'll be able to work this one out :)

    I found this whilst searching for scripts

    Makes interesting reading.

    I'll split the points between nmcdermaid and obahat.  Thanks for your help guys.


    Author Comment

    Sorry, I think I just awarded to nmcdermaid - I'll get these split asap

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 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

    11 Experts available now in Live!

    Get 1:1 Help Now