Database maintenance is one of the most important roles of a DBA. Among the maintenance tasks that a DBA performs, maintaining the integrity of a database is crucial. If you are a DBA or someone who maintains databases, I am pretty sure that you have given priority to DBCC CHECKDB (that checks the allocation, structural and logical integrity of all the objects), to make sure of the consistency of your databases by running it in a timely manner.

This short-article demonstrates how to run DBCC CHECKDB by using one of the given tasks in SSIS under Maintenance Plan Tasks. We will be using Check Database Integrity Task to run the DBCC CHECKDB and Send Email Task to notify the result to someone responsible. You may add more Maintenance Plan Tasks as you wish (usually SSIS Maintenance packages contain more than one Maintenance task). Follow the below steps to make create a new package.

1.       Open the Business Intelligence Development Studio and create a new project by using Integration Services Project template.

2.       Open the Toolbox and drag the Check Database Integrity Task onto the Control Flow tab.

3.       Right click on the task and click on Edit. It will open a dialog box that you can use for setting the database connection.

4.       Click on New to make a new connection to the database server. Fill all necessary input boxes in the Connection Properties dialog box and click on OK.

5.       Expand Databases drop-down and select one of the databases. You may select All user databases. Click OK to save the setting.

6.       If you do not want to check non-clustered indexes, clear the Included Indexes check-box. This makes the DBCC request as DBCC CHECKDB (‘MyDatabase', NOINDEX). Leaving the check-box checked requests to perform the checks on all indexes. Your package may look like this.

7.       Click on OK to save the setting.

Now you can run the package and see whether the selected database is okay or not. The task will end up colored red, if it produces any errors. Since the goal of this package is to capture errors if any exist, and send it to the relevant person; we will be capturing the error in the OnError event handler and store the description of the error in a variable. Instructions given below are the steps for the procedure:

1.       Right click on the Control Flow tab and click on Variables. This will open the variable window.

2.       Create a new variable called errorMessage in the Variables window and select String as the data type.

3.       Go back to the Control Flow and click on the Check Database Integrity Task.

4.       Click on the Event Handler tab. Make sure Check Database Integrity Task is selected before opening the tab.

5.       In the Event Handler, make sure that Check Database Integrity Task is selected in the Executable drop-down and OnError in the Event Handler drop-down.

6.       Open the toolbox (if it is hidden), drag and drop a Script Task to the Event Handler.

7.       Right click on the task and click on Edit. Select Script from the left pane.

8.       Fill the ReadOnlyVariables as System::errorDescription.

9.       Fill the ReadWriteVariables as User::errorMessage. Your screen should look like below.

10.   Click on Design Script. This will open Microsoft Visual Studio for Applications. Complete the Main code as given below.

Public Sub Main()

Dts.Variables("User::errorMessage").Value = Dts.Variables("System::ErrorDescription").Value.ToString()
Dts.TaskResult = Dts.Results.Success

End Sub

11.   Save and exit from Microsoft Visual Studio for Applications.

12.   Click on OK to exit from Script Task Editor.

13.   Go back to the Control Flow.

We need one more task to be added to the control flow, which is for emailing. The below steps give the instructions for it.

1.       Drag and drop a Send Mail Task to the Control Flow from the toolbox.

2.       Open the Send Mail Task Editor by right clicking on the Send Mail Task and clicking Edit.

3.       Select Mail from the left pane.

4.       Fill the SmtpConnection by adding a connection to the mail server.

5.       Fill From and To with correct email addresses. Fill the Subject too.

6.       Select Variable for the MessageSourceType.

7.       Select User::errorMessage for the MessageSource.

8.       Click on OK to save settings.

9.       Make a Precedence constraint by making connection between Check Database Integrity Task and Send Mail Task.

10.   Open the Precedence Constraint Editor by double-clicking the connection line and the ValueFailure. as

11.   Click on OK to save.

Done! You can run the package and see how it turns out. If the package finds any errors, it will send a mail out with the error description. You may make this package more dynamic by adding more variables for email addresses. Here is a snapshot of the entire package.

Note that SQL Server Service Pack 2 introduced a bug that causes to give out wrong result set from Check Database Integrity Task. Make sure you have applied the SP2 and latest GDR. For more info on that, visit this.

[RATE this ARTICLE, and COMMENT on it ]