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

Find ramblings

Wednesday, December 10, 2014

Biml - Reorganize Index Task

Biml - Task

The Reorganize Index Task via Biml. As always, ADO.NET connection required.

<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_ReorganizeIndex">
            <Tasks>
                <ReorganizeIndex
                    ConnectionName="CM_ADO_DB"
                    DatabaseSelectionMode="All"
                    ObjectSelectionMode="Tables"
                    Name="RO All Tables">
                </ReorganizeIndex>

                <!-- Reorg a specific index -->
                <ReorganizeIndex
                    ConnectionName="CM_ADO_DB"
                    DatabaseSelectionMode="Specific"
                    ObjectSelectionMode="Views"
                    Name="RO Specific View"
                    CompactLargeObjects="false">
                    <Databases>
                        <Database>AdventureWorks2012</Database>
                    </Databases>
                    <Objects>
                        <Object>Production.vProductAndDescription</Object>
                    </Objects>
                </ReorganizeIndex>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

The above Biml describes a package that creates two Reorganize Index Tasks. The first reorganizes indexes on all the tables while the second targets a specific indexed view.

Control flow - Reorganize Index Task

RO ALL Tables

Reorganize all the things!

Reorganize all tables

RO Specific View

Here we reorganize a specific table but notice that this dialog makes it appear nothing is selected. Once you open the combobox or click View TSQL, you'll get a different story.

Reorganize specific view

View TSQL

Notice the expected TSQL shows that we're reorganizing Production.vProductAndDescription and Person.vStateProvinceCountryRegion. Weird

Generated TSQL

No comments: