Welcome to Julian Kuiters Saturday, October 20 2018 @ 07:40 PM AEDT

Using Expressions with SSIS Maintenance Tasks and SelectedDatabases

Integration Services 2005 / SSIS (DTS)

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:

USE [scratch]

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:

"DBCC SHRINKDATABASE(N'" + @[User::DBName] + "', 1)"

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.