Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: problem with workflow in dts designer...looping?

    what im trying to do is:

    1) ftp a file from a server to a local directory,

    2) check the file for its lastmodified date

    3) depending on a constraint, perform a data import.

    However, if the file is not modified, this means that the server thats supposed to ftp and update the file has’nt done its job yet, in this case i would like dts to wait a few minutes and then again go to step 1. I want this to repeat for a whole hour until the data has finally imported OR if it hasnt imported at the end of the hour it will send me an email saying it failed.

    The closest I have come to this type of functionality in DTS designer is picture 1.

    In picture 1 i am using the WAITFOR DELAY '000:02:00' to wait 5 seconds between every step. However To do this would require me to create about 30 iterations to span the whole hour! The activeX portion works fine its just learning the flow in dts designer that is giving me the problems.

    It would be so much easier to do what I did in picture 2, but nothing runs.

    here are the pics:
    http://www.geocities.com/samirrahan/picture1.gif
    http://www.geocities.com/samirrahan/picture2.gif

    I would appreciate any help you can offer.



    Thank you.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I would do it differently....

    Each time I successfully import a file (eg I have found a new file and processed it) I would set the next run date.

    I would then set a job up to run the dts package every X minutes (5 for example) between the hours that you expect the file to turn up.

    The first step of the package would be to check if it is the run date is less then or equal to today, if it is then you continue processing, if not then you halt processing.

    This will solve your problem. Yes, it will mean that the package will run more often then it technically needs to, but it will only do the actual processing once.

    HTH

  3. #3
    Join Date
    Nov 2003
    Posts
    14

    i will try to get that to work

    here is the link to my workflow pictures:
    http://www.geocities.com/samirrahan/index.html

    ideally i would want the dts to stop running as soon as the file import is successful. i guess i could do that on a success by rescheduling the job.

    by the way on another note? do u use the designer or just a vb exe. itself? can this be alot easier if i dont use the designer?

    thanks.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I use the designer....

    There is a method to change the jobs schedule using sql. Using sp_add_jobschedule and sp_delete_jobschedule what you could do is...

    step 1, check for new file if fail end DTS Pakcage else step 2

    step 2, ftp file

    step 3, process file

    step 4, execute sql task - sp_delete_jobschedule

    step 5, execute sql task - sp_add_jobschedule

Posting Permissions

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