DTS Scheduling after input is available!!
-
I have a dts package that needs to run once the input file has been updated. The update time fluctuates. Is there a way to have the DTS fail until the file has been updated? Possible after the Date Modified field has been updated? Or possible contents of the file not updated?
-
You'd need to code something in the DTS package to look for the change in the file.
-
Can you give me some more clues as I am a newbie
-
We use DOS command shell to issue DIR command for the file name and parse the results. In our case we look for the file. When found, it's time to process it. Once processed we delete the file, thus setting up for the next run.
If the file is always there, you could use a similar approach where you use the DIR command and parse out the date/time stamp, comparing to a saved value.
-
He is a good article on this subject.
http://:www.databasejournal.com/features/mssql/article.php/3319261
-
Harold, For some reason the link you sent me is not working. Is there another one?
-
If you have the option - VB.Net can use the FileSystemWatcher to monitor a directory for file activity. I have used this and it works just fine (you can watch for new or updated files by extention).
-
I believe the link had an extra colon... try this:
http://www.databasejournal.com/features/mssql/article.php/3319261
-
The Link worked. I will try to get it to work with this info. If anyone else has any other thoughts please post. Thansk To All.. TS
-
If you have a datetime field in the dataset you are updating, you can do a query to the table and check for today's date using an execute SQL task. Then based on the result you can either fail the package or continue processing.
-
Rather than fail the package, I would use the file scripting object to get the file you want and then examine it's last modified property. If it's within the range you want, you would enable the subsequent step, but if it's not, you would DISABLE the subsequent step. It's more elegant in as much as the DTS package doesn't fail, unless you want the mail message.
When it succeeds you can have an email as part of the step to let you know it's done.
Unfortunately I'm traveling and can't get you a code sample, but if this sounds like something you want to do, let me know, and I'll provide a code sample in the next day or two.
-
Yes I do have a datetime field in my Dataset. Another choice. Do you have any sample sql that does that?? TS
-
I would do something similar to the following:
Function Main()
Dim fso, file
Dim pkg, stpNext
Set pkg=DTSGlobalVariables.Parent
Set stpNext=pkg.Steps("<<Name of next DTS Step>>")
Set fso=CreateObject("Scripting.FileSystemObject")
Set file=fso.GetFile("<<FileName>>")
If file.DateModified (Your logic here) then
--If you want it to stop
stpNext.DisableStep=True
Else
--If you want it to go
stpNext.DisableStep=False
End If
Main = DTSTaskExecResult_Success
Set stpNext=Nothing
Set pkg=Nothing
Set file=Nothing
Set fso=Nothing
End Function
This has the advantage that the package won't fail, so this logic fail doesn't get caught up with a real fail. It elegantly brings the pkg to a halt.
Note, I don't exactly do this, so you may need to tweak the code, but this should be close to what you need.
-
Thanks!! I have a hot project that I need to finish. I will probably get hot and heavy in to this early next week. Thanks for the code.
-
You're welcome.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply