Slowly changing Dimensions

The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

1. Matching incoming rows with rows in the lookup table to identify new and existing rows.
2. Identifying incoming rows that contain changes when changes are not permitted.
3. Identifying inferred member records that require updating.
4. Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.
5. Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

Output Types:

• Type 0: Fixed Attribute Output
• Type1: Changing Attributes Updates Output
• Type2 : Historical Attributes Inserts Output
• Type3: Create new column for every updation.
• More on New output, Unchanged Output and Inferred Member Updates Output

Type 0 : It do not allows to update any record inside dimension

Type 1 : It just update each record there is no trace for old record

Type 2 : It add new record in dimension table for each update. It uses flag value or time-stamp to check new value

Type 3 : It creates new column for each updation. This type is not preferable for record which updates more than two times.

Optimizing SSIS Packages

1. Try to perform your data flows in bulk mode instead of row by row. 
2. Do not sort within Integration Services unless it is absolutely necessary. 
3. At certain time where using Transact-SQL will be faster than processing the data in SSIS. 
4. Aggregation calculations such as GROUP BY and SUM. 
5. These are typically also calculated faster using Transact-SQL instead of in-memory calculations by a pipeline. Delta detection is the technique where you change existing rows in the target table instead of reloading the table. 
6. Use partitioning on your target table. This way you will be able to run multiple versions of the same package, in parallel, that insert data into different partitions of the same table. 
7. Make data types as narrow as possible so you will allocate less memory for your transformation. 
8. Do not perform excessive casting of data types – it will only degrade performance. 
9. Use the NOLOCK or TABLOCK hints to remove locking overhead. 
10. If possible, perform your datetime conversions at your source or target databases 
11. Use a commit size of <5000 to avoid lock escalation when inserting. 
12. Heap inserts are typically faster than using a clustered index. 
13. Set Checkpoint So after failure execution start from checkpoint Minimize use of blocking and partially blocking Fast Load Features Optimum Buffering Fast Parse: 
14. Using fast parse can drastically improve performance of your package when using a flat files source or data conversion transform by basically not validating the columns that you specify.