SSIS Data Flow into a dynamically created Excel File - Metadata Error

I have an SSIS package with the following steps:

Create new Excel file with today's date (ie 20240105.xlsx) by copying and renaming a template.xlsx file
Data Flow from SQL into the new Excel file 20240105, into a specific named sheet ie "CustomSheet"

Note that the Excel connection manager uses an Expression to connect to 20240105.xlsx

If I run Step 1 manually and then Step 2, it works just fine. However, if I run the entire package (the Excel file does not yet exist), I get an error on the Excel Destination - "cannot find $CustomSheet "

How can I troubleshoot or bypass this error? Thanks

Solution

Step 2 fails to validate when run as an atomic package because the first step of SSIS package execution is Validation. They take a fail-fast approach lest you get half way through processing and suddenly discover a needed resource is not available.

To resolve this, right click on the Data Flow and select Properties. Find Delay Validation and flip it from the default False to True.

In the next package run, it will Validate all the tasks on start. Validation of the Data Flow will not occur until it is time to run that specific task (But validation always occurs, it's simply a matter of when)