<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Annotations> <Annotation> Disable indexes before dataflow and rebuild them afterwards Marco Schreuder, dec 2012 blog.in2bi.com </Annotation> </Annotations> <# string TableName="[Production].[ProductCategory]";#> <Connections> <OleDbConnection Name="TargetDatabase" ConnectionString="Data Source=.\SQL2012; Initial Catalog=AdventureWorks2012; Provider=SQLNCLI10.1; Integrated Security=SSPI;" CreatePackageConfiguration="true"> </OleDbConnection> </Connections> <Packages> <Package Name="PKG Handle Indexes Gracefully" ConstraintMode="Linear"> <Variables> <Variable Name="IndexList" DataType="Object" /> <Variable Name="DisableStatement" DataType="String" /> <Variable Name="RebuildStatement" DataType="String" /> </Variables> <Tasks> <ExecuteSQL Name="SQL GetIndexList" ConnectionName="TargetDatabase" ResultSet="Full"> <DirectInput> SELECT DisableStatement = 'ALTER INDEX ' + QUOTENAME(i.Name) + ' ON ' + QUOTENAME(SCHEMA_NAME (o.SCHEMA_ID)) + '.' + QUOTENAME(o.name) + ' DISABLE' ,RebuildStatement = 'ALTER INDEX ' + QUOTENAME(i.Name) + ' ON ' + QUOTENAME(SCHEMA_NAME (o.SCHEMA_ID)) + '.' + QUOTENAME(o.name) + ' REBUILD' FROM sys.indexes i JOIN sys.objects o ON o.object_id=i.object_id WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_disabled = 0 AND i.type_desc = N'NONCLUSTERED' AND o.TYPE = N'U' AND QUOTENAME(SCHEMA_NAME (o.SCHEMA_ID)) + '.' + QUOTENAME(o.name)='<#=TableName#>' </DirectInput> <Results> <Result Name="0" VariableName="User.IndexList" /> </Results> </ExecuteSQL> <!--Loop through indexes and disable them--> <ForEachAdoLoop Name="FELC Disable Indexes" SourceVariableName="User.IndexList" ConstraintMode="Linear" > <VariableMappings> <VariableMapping Name="0" VariableName="User.DisableStatement" /> </VariableMappings> <Tasks> <ExecuteSQL Name="SQL Disable Index" ConnectionName="TargetDatabase" ResultSet="None" > <VariableInput VariableName="User.DisableStatement" /> </ExecuteSQL> </Tasks> </ForEachAdoLoop> <Dataflow Name="DFT YourDataFlowTask" /> <!--DataFlow Logic--> <!--Loop through indexes and rebuild them--> <ForEachAdoLoop Name="FELC Rebuild Indexes" SourceVariableName="User.IndexList" ConstraintMode="Linear" > <VariableMappings> <VariableMapping Name="1" VariableName="User.RebuildStatement" /> </VariableMappings> <Tasks> <ExecuteSQL Name="SQL Rebuild Index" ConnectionName="TargetDatabase" ResultSet="None" > <VariableInput VariableName="User.RebuildStatement" /> </ExecuteSQL> </Tasks> </ForEachAdoLoop> </Tasks> </Package> </Packages> </Biml>
Disable indexes before dataflow and rebuild them afterwards
Marco Schreuder
Before the dataflow every non-clustered index in the target table is disabled and after the dataflow every index is rebuild.
published 12.05.12
last updated 12.05.12
Comments
There are no comments yet.