Its been noted by a few people that Expressions cannot be used to configure the Shrink Database Task at runtime. The problem is that the SelectedDatabases property is a collection which cannot be created by an expression.
The same problem occurs in most of the Maintenance Task items of SSIS.
Maintenance Plan Task components are simple wrappers to existing sql
commands. The sql that is run by the Shrink Database Task is similar to:
GO
DBCC SHRINKDATABASE(N’scratch’, 1, TRUNCATEONLY)
Instead of using the Shrink Database Task, I prefer using an Execute SQL Task and applying an expression to that. This also gives you greater control of the shrinking process: you can just shrink an individual data/log file if you wished.
Replacing the Shrink Database Task with the more flexible Execute SQL Task
Remove the Shrink Database Task from the package, and add a Execute SQL Task in its place.
Assuming that the name of the database you want to shrink is in the variable
DBName, you would set the Expression for the SqlStatementSource property of the
Execute SQL Task to:
So Simple.
If you are wanting to shrink multiple databases, you can place the Execute
SQL Task inside a For Each Container.
You could use the
DBCC SHRINKFILE command if you to shrink just the log or an individual data
file.
DBCC SHRINKDATABASE shrinks both all the log and data files for a database.
If you must use the Maintenance Plan Task component, Kirk Haselden has
written an example of
a self modifying package at runtime. You could use this method however in this
case it might make the problem more complex.