SSIS - Skip Missing Files

Question

I have a SSIS 2008 package that calls about 25 other SSIS packages.

Each of those child packages loads a specific file into a table. But sometimes one or more of these input files will be missing.

How can I let a child package fail (because a file is missing) but let the rest of the parent package keep on running?

I've tried increasing the maximum error count on the parent package, the tasks in the parent package that call each child, and in the child package itself. None of that seemed to make any difference. I still get this error when I run it with a file missing:

SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Edit: failpackageonfailure and faulparentonfailure are already all set to false everywhere.

Answer 1

in the properties of the execute package task, you can set the failpackageonfailure and faulparentonfailure. i haven't worked with these, but you can probably play with them to get your desired results.

Answer 2

Side note: for simplicity, I'd set these settings on the parent SSIS package.

There is a MaximumErrorCount values at the Sequence Containers & package level. If you're using this be sure your values are in-sync because the package level settings take precedence.

Another option is the ForcedExecutionValue.

To set this up, load the properties tab for each of container and:

1) ForceExecutionValue to TRUE

This will cause the container to return whatever value you put in the variable (see step #2), despite the outcome of the task(s).

2) ForcedExecutionValue to 0

This acts a return value for that task, and sets it to 0 (true, think "return 0" as in C++).

I hope that helps.

This will cause the package to

• Load the properties using "ForcedExecutionValue" to 0, then Then set the Force

Answer 3

I haven't tried this, but this is how I would approach it. Create a variable for the file name and the child package name. Use a For Each Loop container. Have it go through the location of the files and pull the file names one at a time. Use the file name to change the child package name variable. In the container have the task to run the child package and have the name dynamically set based on the values of the child package name variable.

Then it should only try to run the child packages which have appropriate files.

Answer 4

I have done this kind of scenario development, first plan the package execution method as whenever you will get a file we need to process the package if not either fail or leave the package ultimately our target is to process all the package of files existing. take a variable for all the packages. set the variable to "Y" or "N" on the existing of the file using script component or connection string in the parent package. the existing condition to execute the package on the value of the variable.

This method gave us desired results of process multiple files with different occurences of source files.

thanks prav