22 Mar

SSIS Toolbox: The Maintenance Tasks

SSIS allows you to do do a number of maintenance processes.  I’m not going to go into detail on any of these.  I have no idea if they are on the 70-463 exam but there will probably be one or two.  My gut tells me that if they do, it wouldn’t be heavy on in-depth configuration, rather more along the line of “you have a problem, or want to know something” so you would use “this maintenance task” to do it or find out about it.  And if I’m wrong, well, I’ll have to take my chances.

Back Up Database Task: Exactly what it sounds like.

Check Database Integrity: DBCC CHECKDB statement.

Execute SQL Server Agent Job Task: Executes an Agent job.

Execute T-SQL Statement Task: Execute a T-SQL statement (I’m adding big value now).

History Cleanup Task: Deletes history data in the msdb history tables.  It’s the same as the sp_delete_backuphistory stored procedure.  This task supports deleting backup and restore history, SQL Server Agent Job history, and maintenance plan history.

Maintenance Cleanup Task
: A file cleanup task that is used to clean up backup and maintenance files.  For instance, all files older than 4 weeks.

Message Queue Task: This task sends messages between SSIS packages.  For instance, don’t run until “Check Database Integrity” task finishes.

Notify Operator Task: This task sends messages to a SQL Agent operator. The message can be sent by e-mail, pager, or net send.

Rebuild Index Task: This is equivalent to ALTER INDEX REBUILD.

Reorganize Index Task: This is equivalent to ALTER INDEX REORGANIZE.

Shrink Database Task: Shrinks a database with all the caveats that includes. Equivalent to DBCC SHRINKDATABASE.

Transfer Database Task: Transfers a database between SQL Server instances.

Transfer Error Messages Task: Transfers custom error messages between SQL Server instances.

Transfer Jobs Task: Transfers jobs between SQL Server instances.

Transfer Logins Task: Transfers logins between SQL Server instances.

Transfer Master Stored Procedures Task: Transfers one or more user-defined stored procedures between master databases on instances of SQL Server.

Transfer SQL Server Objects Task: Transfers objects between instances of SQL Server.

Update Statistics Task: Equivalent to the UPDATE STATISTICS statement.

WMI Data Reader Task: Runs queries using WMI to return information about a computer system. For instance, event logs or available memory.

WMI Event Watcher Task: Watches for WMI events. For instance, waiting to run a File System Task until after a file is downloaded to a folder.