2

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?

Tpcool
  • 49
  • 7

1 Answers1

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. enter image description here

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. enter image description here

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. enter image description here

My final Foreach Loop Container looked like this: enter image description here

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • This is good and thorough, although the part where I have been stuck at is the part where we are checking for the existence of the package, where you have the two links. I do not have this project deployed yet; it is still saved locally. The first link is querying a database where my project would not be present. I assume the second link's solution would be more feasible, yet I am not sure how I would implement that script given my circumstances. It seems like I would still need the project stored in a server. Is my only option to have these files on a server? – Tpcool Feb 03 '20 at 16:19
  • I suppose until you deploy the project, you could check for the existence of the files in your local project development folder. If I went this way, I'd probably check the environment first, and if dev, check for the files, and if qa/prod/etc, check for the files on the server. – digital.aaron Feb 03 '20 at 22:38