Branching after a file system task in SSIS without

Question

If a file system task such as a rename fails, for example if the file does not exist, then this is considered an error by SSIS. This means the whole package fails. I can get around it by using a script task, or setting the maximum errors for the package to more than one. The problem with setting maximum errors for the package to more than one is that if an error occurs somewhere else in the package the package won't fail when it should.

So is there any way to somehow swallow the error and still be able to branch based on success or failure of the file system task? I tried sticking the file task into a sequence container with ForceExecutionResult set to Success, but the package still fails saying maximum error count reached.

Answer 1

Check the FailPackageOnFailure and FailParentOnFailure properties of the FileSystem task and make sure they are set to False. Also, increase the MaximumErrorCount property of the package.

This combination will allow the task to fail and the package to still complete successfully.

Answer 2

Here is one possible option. You can achieve the requirements mentioned in the question by changing the Precedence Constraint after the File System Task to Completion. Following example shows how this can be done. The example was created using SSIS 2008 R2 but holds true for previous versions as well.

Step-by-step process:

1. Create an SSIS package and create two variables as shown in screenshot #1. Assign some non-existent file path values to the variable.

2. On the package's Control Flow tab, place a File System Task, Script Task and Data Flow Task as shown in screenshot #2. Script Task and Data Flow Task are dummy here and the tasks are not configured to do any actions.

3. Configure the File System Task as shown in screenshot #3.

4. Screenshot #4 shows package execution and as expected the File System Task failed because the file paths don't exist. Also, the package stopped executing the tasks after the File System Task because of the error.

5. Right-click on the Precedence constraint arrow between File System Task and Script Task and select Completion as shown in screenshot #5. The arrow should turn from green color to blue color.

6. Screenshot #6 shows package execution after the precedence constraint change. The File System Task still failed but the other tasks continued to execute. Earlier, the condition was to execute the Script Task only if the File System Task succeeded. Now, the condition is to execute the Script Task after File System Task completes its execution (irrespective of Success or Failure).

7. If you would like to have the package take completely two different paths based on success/failure. You can do so as shown in screenshots #7 and #8. The red arrow indicates that the path will be taken on Failure of the File System Task and the green arrow indicates that path will be taken on Successful execution of the File System Task. I had a file created in the path C:\temp\Source.txt before the execution of package shown in screenshot #7. Once the package executed, the file was renamed and the path was no longer. Hence, the package failed as shown in screenshot #8.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Answer 3

I haven't tried this with a FS task, but it works well for dataflows... What about wrapping your filesystem task in a sequence container... If the step fails, the container fails, you could simply output from the container to a success/failure path....

Answer 4

I know that this is an old question, but it might help someone looking for an answer.

Combined with user756519's quite good answer, stopping propagation of the error prevents the package from failing. This means that you can effectively use the failure as a branch without worrying about it mincing your package.

See cfrag's answer on this question for more information. That answer points to this blog entry which explains how to set the propagate variable for the OnError event handler (make the event handler, open variables view, show system variables, set Propogate to false).

Answer 5

prashant_sp answer is the right one, you just have to continue unconditionnally to the next task, by not selecting the "on success" workflow precedence constraint, but the "redirect" one which will always continue, even if your FS task fails (folder does not exist, etc...)

Kind regards

Answer 6

Use a expression and constraint combination on the "precedence constraint" connector. return success always from your previous task (it may be a script looking for the file) and if file found, set a variable. in the expression of the "precedence constraint" check for the variable value and return true or false. If your expression evaluates to false, the package will nto continue and yet you will not see any error. - Mayukh