Presedence Constraint from File System Task not working as expected
-
I have a Foreach Loop Container with 2 tasks. A File System Task set to delete a file, and an Execute SQL Task set to Update the database that the file was deleted. Basically, if the file wasn't deleted (perhaps it didn't exist) I don't want the Execute SQL Task to run. The Precedence Constraint between the two tasks is set to "Success", so I would expect the Execute SQL Task to run only if the Delete File operation was successful.
I set up a test where one of the files to be deleted doesn't exist. However, the File System Task still passes a "Success" through the Precedence Constraint; and the Execute SQL Task gets run anyway.
Does anyone know how I can get the File System Task to pass a "Failure" to the Precedence Constraint when the Delete File process fails? To put it another way, does anyone know what constitutes a failure for a File System Task?
Thanks
-
fstop (8/16/2010)
I have a Foreach Loop Container with 2 tasks. A File System Task set to delete a file, and an Execute SQL Task set to Update the database that the file was deleted. Basically, if the file wasn't deleted (perhaps it didn't exist) I don't want the Execute SQL Task to run. The Precedence Constraint between the two tasks is set to "Success", so I would expect the Execute SQL Task to run only if the Delete File operation was successful.I set up a test where one of the files to be deleted doesn't exist. However, the File System Task still passes a "Success" through the Precedence Constraint; and the Execute SQL Task gets run anyway.
Does anyone know how I can get the File System Task to pass a "Failure" to the Precedence Constraint when the Delete File process fails? To put it another way, does anyone know what constitutes a failure for a File System Task?
Thanks
Please see the below post..
http://www.bigresource.com/Tracker/Track-ms_sql-oCXRB3RC/
Work around is to add the add the file system task and execute sql task inside for-each loop so that it would loop through only if file exists.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
Thanks for your suggestion and the link. Both tasks are already in the Foreach Loop. I added another File System Task above the one that deletes the file, and used the Set Attributes mode as noted in the link: "If you need it to fail, then a quick workaround for your package would be to add a second File task, and choose the Set attributes mode. That will fail if the file is not found."
The problem is that it stops all processing if it fails. Ideally, I would like the first File System Task to test if the file exists. If it does, continue to the 2nd File System Task to delete the file. If it doesn't, pass back out to the Foreach Loop to process the next record. Does anyone know how to accomplish that?
-
The problem is that it stops all processing if it fails. Ideally, I would like the first File System Task to test if the file exists. If it does, continue to the 2nd File System Task to delete the file. If it doesn't, pass back out to the Foreach Loop to process the next record.
You can connect green arrow with constraint "success" of file system task 1 to file system task 2 and another green arrow with constraint "failure" of file system task 1 directly to foreach loop.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
That would be a great solution, but it won't let me. It comes back with an error: "Cannot create connector. Cannot connect the executables from different containers." Sigh...
Also, if I right click on the Foreach loop container and select Add Precedence Constraint, the only ones listed are the 2 control tasks OUTSIDE the Foreach Loop and the Foreach Loop Container itself. I can't get it to show any tasks inside the Foreach Loop Container.
-
fstop (8/18/2010)
That would be a great solution, but it won't let me. It comes back with an error: "Cannot create connector. Cannot connect the executables from different containers." Sigh...Also, if I right click on the Foreach loop container and select Add Precedence Constraint, the only ones listed are the 2 control tasks OUTSIDE the Foreach Loop and the Foreach Loop Container itself. I can't get it to show any tasks inside the Foreach Loop Container.
My bad! I did not realize your tasks are inside the for-each container. If your delete task are already inside "for each" loop it would go into delete task unless there are files to be deleted. I am wondering how you are getting error at the first step itself? Is your for-each component pointing to the folder & files which you intent to delete?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
Here is an image of the package:
-
fstop (8/18/2010)
Here is an image of the package:Did you try adding another for-each component inside the for-each resultset pointing to the folder & file to be deleted?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
With all due respect, I don't understand how that would work. It just doesn't make sense to me. Can you explain in more detail perhaps?
-
fstop (8/18/2010)
With all due respect, I don't understand how that would work. It just doesn't make sense to me. Can you explain in more detail perhaps?Let me explain in detail.
Hope the first for-each loop is to loop the records in the record set storing the folder & file path in a variable or so.
In second for-each task using property expression you would map the folder & file to variables created by the first for-each task.
During run-time based on the folder values for each records if there is a match it would go inside the second for-each task and delete records. Is it clear now?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
Thanks. Yes, I understand. However that isn't possible because each FileName variable passed from the recordset is a fully-qualified file name. Thus, there is no 2nd set of records to loop through. It's simply a process of Verify the file exists, Delete the file and update the database if it does.
Perhaps I need to rethink the process and replace everything in the Foreach loop with a Script task that handles it all.
-
fstop (8/18/2010)
However that isn't possible because each FileName variable passed from the recordset is a fully-qualified file name. Thus, there is no 2nd set of records to loop through. It's simply a process of Verify the file exists, Delete the file and update the database if it does.Perhaps I need to rethink the process and replace everything in the Foreach loop with a Script task that handles it all.
You can decide on it but it is still possible to map it if you define the property "Directory" & "FileSpec" to the folder like below..
Directory: C:\test
FileSpec: xyz.txt
You can mention the filename in Filespec instead of something like *.txt or so. It will still work. You need to split these using expression builders if you are getting it from your table as one field.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply