A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, January 29, 2014

Biml - Check Database Integrity Task

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.
Control Flow with a Check Database Integrity Task

The Task itself allows you to select a connection manager and the database selection mode.
Check Database Integrity Task - specify connection and database selection mode

The details screen allows for the selection of specific databases as well as behaviour for offline mode.
Check Database Integrity Task - select databases

No comments: