SSIS ETL Failure - Failed to acquire connection "DestinationConnectionExcel"

Question

Essentially, I was tasked to add enhancements to Power Reports; however, I am finding out that I am fixing current issues in the ETL. The ETL was set up by another developer and they set it to run out of an Agent Job. The first step deletes all of the Excel files that are the destinations and then each step (for example 2024 Invoices.dtsx) will recreate the Excel file in one step and then push data to Excel file in another step.

My main issue is that one one day the 2024 Invoices will fail with the error message (Failed to acquire connection "DestinationConnectionExcel"). The next day it could be 2023 Invoice dtsx package that failed but the 2024 Invoices will be ok. So I feel like I am just plugging holes in the dam each day.

The ETL is designed so that the first task is deleting all of existing Excel files it previously posted to, then the second task recreates the file and the third task is pushing thta data into a new Excel file. It is hard to figure out because one day package A runs fine and the second day it fails. It seems to always fail on the third step during a validation - thus cannot acquire connection to the Excel file that was created. My guess, is that for some reason, is that dropping the files and recreating is making the third step fail sometimes.

Any suggestions on how to fix without tearing the whole EtL process down and then importing data into a database instead of Excel files?

Thanks!

I am just rerunning the packages daily to produce the Excel file but this is not a long term solution.

Answer 1

It sounds like you have a race condition with your package. By default, the first step of an SSIS package is Validation. Every reference is checked before any work begins lest you get 90% of the way through a run only to realize the final artifact has changed. A fail fast approach to validation.

Validation must occur. There's no option there, that's how the fine engineers at Microsoft built the tool. But, you can defer validation until the point of reference, which is what you likely need to do.

With your updated information, step 1 is deleting out all the existing files. Assume there was an issue with step 2 (create empty files). When the package starts, step 3 (load file) is going to fail because there's no file to validate. Or maybe the file is there but instead of Sheet1, it expects a worksheet called Data. There's a host of possibilities, especially once things get dynamic with regard to names and such.

To resolve this, every object in SSIS has a DelayValidation property which defaults to False. Flip that to True on ... Task 2, 3 and probably the associated Excel Connection Manager(s). My idea being that we'll ensure the tasks that use the Excel files are only validated when they should be checked and the Connection Manager---because I can't remember exactly how/when it needs to be valid in the validation process.