Results 1 to 9 of 9

Thread: Data import

  1. #1
    Join Date
    Dec 2003
    Posts
    9

    Unanswered: Data import

    I am importing data from text files nightly and I am wondering if there is a way to have SQL recognize whether the text file has been updated before the job runs. The text files come over from our Unix box and every once in a while the transfer fails, so when this happens I don't want the SQL jobs to run because I just get duplicate data and not updated data. I don't know if this is possible, but I am just wondering. Thanks.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Are you saying that if the process fails, the old files still exist. Do the filenames change - and what about date/time for the files ? Also, what would happen if you deleted or moved the files when you had successfully imported the data ? And what are you using now to import the data ?

  3. #3
    Join Date
    Dec 2003
    Posts
    9
    Yes, the files are overwritten nightly. I am using a DTS package to import from the textfile. I thought about just deleting the files after the import, then the jobs would just fail if no files were there, but I just wondered if there was a way for SQL to recognize the date on the file before running the job.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    You can do either - run the dts based on file existence or on file date/time. I would add a file existence step in your dts package and a file move/delete step after the import was successful.

  5. #5
    Join Date
    Dec 2003
    Posts
    9
    How would I word the existence step based on date/time? Thanks

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    You can create an activex script task using the filesystemobject.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Have you worked with the filesystemobject - if not I can show you an example.

  8. #8
    Join Date
    Dec 2003
    Posts
    9
    Just created it and it seems to be working, but I have it setup only to see if the file exists, if it doesn't then the package fails, which is what I want. Is there a way to have it check for file date and fail if it is not correct instead of just if it exists?

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the File object and read the DateCreated property. You can create the File object using the GetFile method of the filesystemobject.

Posting Permissions

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