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.
http://msdn.microsoft.com/en-us/library/ms141164.aspx

Check Database Integrity: DBCC CHECKDB statement.
http://msdn.microsoft.com/en-us/library/ms139858.aspx

Execute SQL Server Agent Job Task: Executes an Agent job.
http://msdn.microsoft.com/en-us/library/ms137858.aspx

Execute T-SQL Statement Task: Execute a T-SQL statement (I’m adding big value now).
http://msdn.microsoft.com/en-us/library/ms139753.aspx

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.
http://msdn.microsoft.com/en-us/library/ms139794.aspx

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.
http://msdn.microsoft.com/en-us/library/ms345177.aspx

Message Queue Task: This task sends messages between SSIS packages.  For instance, don’t run until “Check Database Integrity” task finishes.
http://msdn.microsoft.com/en-us/library/ms141227.aspx

Notify Operator Task: This task sends messages to a SQL Agent operator. The message can be sent by e-mail, pager, or net send.
http://msdn.microsoft.com/en-us/library/ms140060.aspx

Rebuild Index Task: This is equivalent to ALTER INDEX REBUILD.
http://msdn.microsoft.com/en-us/library/ms137718.aspx

Reorganize Index Task: This is equivalent to ALTER INDEX REORGANIZE.
http://msdn.microsoft.com/en-us/library/ms141243.aspx

Shrink Database Task: Shrinks a database with all the caveats that includes. Equivalent to DBCC SHRINKDATABASE.
http://msdn.microsoft.com/en-us/library/ms141819.aspx

Transfer Database Task: Transfers a database between SQL Server instances.
http://technet.microsoft.com/en-us/library/ms141204.aspx

Transfer Error Messages Task: Transfers custom error messages between SQL Server instances.
http://msdn.microsoft.com/en-us/library/ms141674.aspx

Transfer Jobs Task: Transfers jobs between SQL Server instances.
http://msdn.microsoft.com/en-us/library/ms137568.aspx

Transfer Logins Task: Transfers logins between SQL Server instances.
http://technet.microsoft.com/en-us/library/ms137870.aspx

Transfer Master Stored Procedures Task: Transfers one or more user-defined stored procedures between master databases on instances of SQL Server.
http://msdn.microsoft.com/en-us/library/ms141256.aspx

Transfer SQL Server Objects Task: Transfers objects between instances of SQL Server.
http://msdn.microsoft.com/en-us/library/ms142159.aspx

Update Statistics Task: Equivalent to the UPDATE STATISTICS statement.
http://msdn.microsoft.com/en-us/library/ms137599.aspx

WMI Data Reader Task: Runs queries using WMI to return information about a computer system. For instance, event logs or available memory.
http://msdn.microsoft.com/en-us/library/ms141744.aspx

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.
http://msdn.microsoft.com/en-us/library/ms141130.aspx