Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Tampa, FL (USA)
    Posts
    9

    Unanswered: Verify data source viable before proceeding

    I've completed my first SQL project, for which I've built a DTS Package. First thing it does it drop all records in the destination table, before importing new records from a txt file and then massaging them.

    After I got done, I realized that if the data source is not available for some reason, the records will still be dropped, the process will fail, and the destination table will be left empty. In this case, leaving the existing records intact would be preferable to not having any.

    How can I test that the txt file exists before dropping the records?

    Thanks,

    Randy

    ps: Users will maintain a link to the table. I plan to update the table after business hours. If someone happens to have their linked application open while I'm trying to update the table, will it fail?

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50

    Post

    I am using the following mechanism in my DTS packages (see the atachement)

    You "Dir ... > Filelist.txt", then import the content of Filelist.txt in a temporary table. You select the first line of the table and output the parameter in a global variable. Then, an ActiveX task checks that the variable is not null, that is the source text file exists
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2004
    Location
    Bermuda
    Posts
    40
    As marp says:
    Add an ActiveX module @ the beginning of the Package.
    Using VBS and the FSO object, test for the existence of the filename (see code below).

    Then connect this module to the next one with an 'On Success' workflow.

    Function Main()
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    IF fso.FileExists(<filename+path>) = FALSE THEN
    ........
    Main = DTSTaskExecResult_Failure
    ELSE
    .....
    Main = DTSTaskExecResult_Success
    End Function

    RobbieD
    Sunny Times...

  4. #4
    Join Date
    Jul 2003
    Location
    Tampa, FL (USA)
    Posts
    9
    Thanks for the replies - appreciate the help! I will let you know if I have any other questions.

    Randy

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You 'll have more flexibility and less headaches with a sproc and bcp...

    But that's MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    Tampa, FL (USA)
    Posts
    9
    Brett - how would a sproc and bcp be better? I was thinking of bulk loading, and parsing the file afterwards? Is that what you mean? I'm anxious to learn better ways.

    Robbie - thanks for the code for that ActiveX module! It works like a charm.

    Randy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •