SSIS 2008R2 – Use Package Config Value in Expression

This is due to blocking caused by the delete task accessing the metadata from the database during the Pre-Execution phase.

The locks can be found using sys.dm_tran_locks and it will have -2 value as OP founded. More details could have been found out using Profiler.

The blocking can be resolved by changing the ValidateExternalMetadata property to FALSE or as OP mentioned using a DELETE statement.

Reference: KB2253391

When a package receives a signal to start, one of the tasks it performs is to ensure everything it needs is available and has the appropriate signatures. SSIS is all about the metadata. The idea behind this is that a package should blow up before it does any work if it can tell at the start that an error will occur (can't connect to a server, table doesn't exist, data type is incompatible, etc). It's generally a good thing, why start something you know you can't finish and potentially leave yourself in a messy/inconsistent state.

In your scenario, it is validating that this file is there and since it isn't, because it's generated at run-time, it fails. Therefore, you have to flag it such that the validation occurs right at the moment the resource is needed. As @mike Henderson indicated, you'll need to go into the properties of the objects and change the DelayValidation value from False to True. You will probably need to set it on both the Connection Manager and the Data Flow Task that uses it. Click once on the item, hit F4 or right click and select Properties.