Post reply

Check for records, export then FTP and update

  • October 15, 2004 at 5:40 am

    #60461

    Hi,

     

    Just want to push this past you guys/gals to see if my theory on how to do this is the best or not?

    We are building an eBusiness solution and need to export new records when they are created to a text file, ftp that text file to our back office system and update a table to say it has been sent. We also need to build a similar interface coming back in, look for a fil, if its there import it and then issue a command to another server to run a process on it.

    For the outgoing interface I was considering a procedure that looks for a new record without a datestamp saying its been sent, if it finds a record exec a new procedure that exports it, ftp's the file and then updates the record. considering bcp for the export and a simple ftp script?

    The incoming one is more difficult, how can I check if a file exists?

    Pretty new to this so any help or samples would be appreciated.

    Kind Regards

    Andy

  • October 15, 2004 at 7:31 am

    #526893

    I've been looking through sqldts.com and seen some interesting dts plans for ftping and importing files.

    Anyone had any experience of these?

    Andy

  • October 17, 2004 at 9:22 am

    #526989

    hey Andy

    i would like to know is it possible to call the system functions such as"bcp" from sqlserver 2000 itself.?

    and also how can we create our own extended procedures?

    plz help me in this regard

    bye

    Rajiv.

  • June 12, 2006 at 4:51 am

    #643028

    I have. I think the best way would be to plan out the steps. You will need to use Global Variables to pass the file name you have exported to to the FTP object (unless the same name is used)

    also, i would pay attention to the firewall settings for your FTP Connection (we spent 1 week on such a problem, and the firewall was causing bad connections and CRCs on uploaded files)

    good luck

  • June 12, 2006 at 9:56 am

    #643158

    Referring to the earlier post, you can call BCP by using xp_cmdshell. Building your own extended procs requires C++, sometimes you can get nearly the same functionality by using the sp_OA procs which let you drive a com object from TSQL. The file scripting object (VBScript) will let you detect if a file is present.

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply