Tuesday, March 19, 2013
Define Constraint Based Loading and Target Load Order? Whats is the difference between the two?
Constraint Based Loading is a session level property. When this option is selected in session properties, the Integration Service orders the target load on a row by row basis. For every row, the transformed row is loaded first to the primary key table and then to the secondary key table. Target tables in the mapping should have 1 active source (Targets in separate pipelines are not applicable) and should have key relationships(non-circular in nature). Also the session option ‘Treat Source Rows As’ should be set to Insert. Updates can not be used with constraint based loading.
Target Load Order Group is a collection of source qualifiers, transformations and targets in a mapping. Thus a mapping containing more than 1 pipeline is eligible to Target Load Order. Target Load Order sets the order in which Integration Service sends rows to targets in different target load order groups.
1. Constraint Based Loading requires targets to have only 1 active source while Target Load Order is for targets having different active sources.
2. Constraint Based Loading can not be done to maintain referential integrity for updates while Target Load Order can be used to maintain referential integrity when inserting, deleting or updating tables that have primary key and foreign key constraints.