Database Maintenance Tasks in SSIS

Tools for keeping your SQL indexes in check.

Do you have a large data import that leaves your indexes less than perfect? Do you want to backup your current database before you run a purge package? You are in luck; SSIS contains a handful of database maintenance tasks for these and other special occasions. If you have worked with database maintenance plans in SQL Server 2005 or SQL Server 2008, then you are already familiar with what can be done in SSIS. You may or may not know that maintenance plans are just SSIS packages that you built with a different tool. So why is this all important? Well, as I stated earlier, when you do things to your data, you will change the state of index fragmentation and if you are deleting or updating data, you could potentially lose information. By using the maintenance plans tasks in SSIS you can build in backup, reindexing, and other maintenance processes to your package. The best thing about this is that they will run in the bounds of your control flow so you won't have to do try and guess at scheduling as you would if you were trying to coordinate SQL Agent Jobs. Most of the tasks are very self explanatory but here is a complete list anyway.

  • Back Up Database - Performs database backups
  • Execute SQL Server Agent Job – Starts a SQL Server Agent job
  • Check Database Integrity – Runs DBCC CHECKDB to verify database integrity
  • Execute T-SQL Statement – Run provided T-SQL against a SQL Server.
  • History Cleanup -  Cleans up history in MSDB including backup history, maintenance plan history and job history
  • Maintenance Cleanup – Cleans up backup files or maintenance plans text reports
  • Notify Operator – Sends a notification to a SQL Operator
  • Rebuild Index – Rebuilds one or more indexes
  • Reorganize Index – Reorganizes one or more indexes
  • Shrink Database – Use to shrink a file or files in a database
  • Update Statistics – Updates database statistics

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Surface and ARM: Why Microsoft Shouldn't Follow Apple's Lead and Dump Intel

    Microsoft's current Surface flagship, the Surface Pro X, already runs on ARM. But as the ill-fated Surface RT showed, going all-in on ARM never did Microsoft many favors.

  • IT Security Isn't Supposed To Be Easy

    Joey explains why it's worth it to endure a little inconvenience for the long-term benefits of a password manager and multifactor authentication.

  • Microsoft Makes It Easier To Self-Provision PCs via Windows Autopilot When VPNs Are Used

    Microsoft announced this week that the Windows Autopilot service used with Microsoft Intune now supports enrolling devices, even in cases where virtual private networks (VPNs) might get in the way.

  • Most Microsoft Retail Locations To Shut Down

    Microsoft is pivoting its retail operations to focus more on online sales, a plan that would mean the closing of most physical Microsoft Store locations.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.