<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>