How to downgrade a statement for SSIS

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

Given your symptoms, I believe you have changed your PackageProtectionLevel from the default setting of EncryptSensitiveWithUserKey to DontSaveSensitive This is a good thing in my world view but it does require that you now manage sensitive data.

The hackish approach you are using is to create a password Variable and then use that in an Expression as part of the Connection String. That works but you are now storing a password in clear text which is what the default PackageProtetionLevel was designed to protect against.

Fixing it "right" in my mind would involve storing that password as an external, secured resource. We always used SQL Server configurations in our packages and extended the base table with a column indicating whether this configuration was sensitive or not. In production, we, the developers, only had access to rows that were set as insensitive. The service account which ran all the packages had no such restrictions.

Alternate approaches are to store these configuration values in an XML file, registry key, environment variable or a parent package.

You can also skip the configuration route if you know your packages will only ever be launched through method X (batch file, Agent Job, etc). In that case, simply provide the password as a run-time value to the invoking process and all will be well.