October 26, 2005 at 3:12 am
I have a DTS package that is scheduled to run every half hour to pick up a csv file on the network.
When the csv file is present it loads the data into a table so some users can query against it.
The problem I have is if there is no file present (there doesn't always have to be) the SQL Server Agent job reports a failure. I have an active x script that checks for the file and has two workflow arrows from it, one is success which carries on with the rest of the package the other is failure which Ends the DTS dynamic properties.
Is there a way of getting the job to report success if there is no file there for it to pick up?
October 26, 2005 at 6:58 am
Not sure why you would need to report the package as successful if there is no file found. Sounds like it should be reported as a failure.
Personally, I would split the ActiveX script to perform 2 separate processes. The first process would check for file existence, the second would perform the table load. The processes could be ActiveX Script Task or Execute SQL Task.
Report the results of the first process to a GV (Flag to indicate file exists/dosen't exist). This process will always indicate success.
The second process will then check the GV flag to see if file exists before it executes. If GV says it's there, continue processing. Otherwise, exit process.
Hope this helps. Don.
October 27, 2005 at 6:24 am
You want the workflow as described in http://www.sqldts.com/default.aspx?218. This allows you to check for files and bypass the processing branch if there are no files present. The job will still report success.
I assume you run this way because files aren't always there and that's not a bad thing? If so another way to approach this is to write a FileWatcherService in .Net that watches the directory for that file and then executes the DTS package (without the workflow) when a file shows up.
October 29, 2005 at 3:29 am
Thanks for the replys, rschaeferhig - you are right in saying that it is not a bad thing if there is no file there for the package to find. All I am looking to do is look every half hour for a small csv that can be loaded into a temp table which can then be queried. I found something at this link http://www.sqldts.com/default.aspx?211 using ActiveX workflow script, but with not having a vast amount of DTS knowledge i am having difficulties in coding the checking mechanism.
I will have another go of this over the weekend and also look at http://www.sqldts.com/default.aspx?218 for ideas.
thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply