Biml - Check Database Integrity Task

The SSIS Check Database Integrity Task allows you to verify the consistency of your databases. In this example, I'll check two specific database: AdventureWorksDW2012 and AdventureWorks2012. Via the DatabaseSelectionMode, you can specify whether it should check All, System, User or Specific databases.

Biml

The Biml for Check Database Integrity task requires an ADO connection manager.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_CheckDatabaseIntegrity">
            <Tasks>
                <CheckDatabaseIntegrity ConnectionName="CM_ADO_DB" DatabaseSelectionMode="Specific" Name="CHK Specific DBs" IgnoreDatabasesInNotOnlineState="true">
                    <Databases>
                        <Database>AdventureWorks2012</Database>
                        <Database>AdventureWorksDW2012</Database>
                    </Databases>
                </CheckDatabaseIntegrity>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Results

Right clicking on that Biml file will result in a new package being built called Task_CheckDatabaseIntegrity.dtsx

The Control Flow will have a single Task in it along with our ADO.NET Connection Manager.

The Task itself allows you to select a connection manager and the database selection mode.

The details screen allows for the selection of specific databases as well as behaviour for offline mode.