How to Avoid Errors in Dynamic "Execute Package Task" in SSIS?

Question

In SSIS, I have an Execute Package Task that I have calling packages dynamically through the PackageName property in the Expressions property. Basically, for each file in my directory, call the package with the same name. My issue is that there may be instances where one or some of the files in the directory will not have a package associated with it, and that will always result in an error. How can I preemptively catch this error and deal with it so that the process does not stop altogether?

The solution I had in mind was using a Script Task in which I could iterate through all of the packages available in my project and compare it against the current file in the Foreach loop, and if no such package exists for the current file I would skip trying to process it. However, I could not easily find a way to enumerate through my project's package names. I attempted to do it via the method explained in this answer, but I could not seem to get it working for my purposes (I don't have this deployed on a server right now, which I think is what it is saying I should do, but I don't feel like I should need to just to accomplish this).

Is there a way to check if a package name for my project exists using a Script Task? Or is there any way in general to preemptively catch this error so I can allow the project to continue running?

Answer 1

The brute force method would be to set all your MaximumErrorCount properties to 0, and then your package would never stop for a failure.

The correct design pattern would be to just not try to call the package if it doesn't exist. You can create branching logic in your loop that will only call the Execute Package Task when the package exists.

First, create a boolean variable, I'll call mine PackageExists.

Then inside your Foreach Loop Container, create a task to check the existence of the package. I use a version of a query billinkc posted as answer to another question here. You could also use a Script Task and make use of the C# methods described here. Either way, you want to assign a boolean value to User::PackageExists.

Next, create two Sequence Containers. Click on the first, and add a new Expression property to it. Set the Disabled property value to an expression that checks User::PackageExists for a FALSE value. This container will hold your Execute Package Task.

Click on the second Sequence Container and do the same, but check for a TRUE value. In this container I have chosen to set another variable equal to the missing package name. I could use this in a subsequent Execute SQL Task to log the missing package name. You can do whatever you want here, including leaving this container empty.

My final Foreach Loop Container looked like this: