Incremental Load Design Pattern

gravatar

Andy

The Incremental Load Design Pattern, taken from the book SSIS Design Patterns (http://www.amazon.com/SSIS-Design-Patterns-Matt-Masson/dp/1430237716).

published 02.06.13

last updated 02.06.13


Share

Tags

  • Incremental
  • Incremental
  • Load,
                            


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<Connection Name="SSISIncrementalLoad_Source" ConnectionString="Data Source=(local);Initial Catalog=SSISIncrementalLoad_Source;Provider=SQLNCLI11.1;Integrated Security=SSPI" />
		<Connection Name="SSISIncrementalLoad_Dest" ConnectionString="Data Source=(local);Initial Catalog=SSISIncrementalLoad_Dest;Provider=SQLNCLI11.1;OLE DB Services=1;Integrated Security=SSPI;" />
	</Connections>
	<Packages>
		<Package Name="IncrementalLoadPackage" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">
			<Tasks>
				<ExecuteSQL Name="Truncate stgUpdates" ConnectionName="SSISIncrementalLoad_Dest">
					<DirectInput>Truncate Table stgUpdates</DirectInput>
				</ExecuteSQL>
				<Dataflow Name="Load tblDest">
					<PrecedenceConstraints>
						<Inputs>
							<Input OutputPathName="Truncate stgUpdates.Output" />
						</Inputs>
					</PrecedenceConstraints>
					<Transformations>
						<OleDbSource Name="tblSource Source" ConnectionName="SSISIncrementalLoad_Source">
							<ExternalTableInput Table="dbo.tblSource" />
						</OleDbSource>
						<Lookup Name="Correlate" OleDbConnectionName="SSISIncrementalLoad_Dest" NoMatchBehavior="RedirectRowsToNoMatchOutput">
							<InputPath OutputPathName="tblSource Source.Output" />
							<DirectInput>SELECT ColID, ColA, ColB, ColC FROM dbo.tblDest</DirectInput>
							<Inputs>
								<Column SourceColumn="ColID" TargetColumn="ColID" />
							</Inputs>
							<Outputs>
								<Column SourceColumn="ColA" TargetColumn="Dest_ColA" />
								<Column SourceColumn="ColB" TargetColumn="Dest_ColB" />
								<Column SourceColumn="ColC" TargetColumn="Dest_ColC" />
							</Outputs>
						</Lookup>
						<OleDbDestination Name="tblDest Destination" ConnectionName="SSISIncrementalLoad_Dest">
							<InputPath OutputPathName="Correlate.NoMatch" />
							<ExternalTableOutput Table="dbo.tblDest" />
						</OleDbDestination>
						<ConditionalSplit Name="Filter">
							<InputPath OutputPathName="Correlate.Match"/>
							<OutputPaths>
								<OutputPath Name="Changed Rows">
									<Expression>(ColA != Dest_ColA) || (ColB != Dest_ColB) || (ColC != Dest_ColC)</Expression>
								</OutputPath>
							</OutputPaths>
						</ConditionalSplit>
						<OleDbDestination Name="stgUpdates" ConnectionName="SSISIncrementalLoad_Dest">
							<InputPath OutputPathName="Filter.Changed Rows" />
							<ExternalTableOutput Table="dbo.stgUpdates" />
						</OleDbDestination>
					</Transformations>
				</Dataflow>
				<ExecuteSQL Name="Apply stgUpdates" ConnectionName="SSISIncrementalLoad_Dest">
					<PrecedenceConstraints>
						<Inputs>
							<Input OutputPathName="Load tblDest.Output" />
						</Inputs>
					</PrecedenceConstraints>
					<DirectInput>
						Update Dest
						Set Dest.ColA = Upd.ColA
						,Dest.ColB = Upd.ColB
						,Dest.ColC = Upd.ColC
						From tblDest Dest
						Join stgUpdates Upd
						On Upd.ColID = Dest.ColID
					</DirectInput>
				</ExecuteSQL>
			</Tasks>
		</Package>
	</Packages>
</Biml>

                        

The Incremental Load Design Pattern, taken from the book SSIS Design Patterns (http://www.amazon.com/SSIS-Design-Patterns-Matt-Masson/dp/1430237716). Designed for SQL Server 2012 using BidsHelper 1.6. The Biml produces an incremental load pattern that uses brute force change detection (a lookup detects rows where business keys from the source exist in the destination, a conditional split compares each non-business-key source field to its destination counterpart). The pattern also stages updates in the Data Flow Task and applies a set-based update in the Execute SQL Task that follows the load.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

dream

4:23pm 05.04.15

Some time we need to rename the "ConditionalSplit - Default Output name"... I cannot figure out how to do it ?

gravatar

manu

12:43am 06.24.16

How to Implement this If I have 100 databases and each database having nearly 300 tables. All databases are similar.