Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer by Brian Knight & Devin Knight & Mike Davis & Wayne Snyder

Author:Brian Knight & Devin Knight & Mike Davis & Wayne Snyder
Language: eng
Format: epub
Publisher: Wiley
Published: 2012-11-03T16:00:00+00:00

If you want to handle deletes, it is likely that you will have to look for rows in the destination table that do not exist in the source. If a row exists in the destination, but not in the source, it must have been deleted from the source, so you would then delete it from the destination. You can do this by scanning the actual destination table, or by keeping a copy of the key values that exist in the destination table in a staging area. Using a key table in staging reduces the impact of lookups on the destination and allows the package to run more quickly.

When your package runs, it should find those rows in the source that have been inserted, updated, or deleted within a time range. It is very common for these packages to run nightly, gathering all of the changes from the prior day. As an example, the package may run every night at 2:00 a.m., looking for changes from midnight to midnight the prior day. You might store the last completed time range in a control table, and use that as the starting point for your next time range. You are going to look in the destination table for the most recent change and find all rows in the source with a modified date after that time and before midnight of the current day.

How you handle the time range is very important and is worth some thought and planning. Your package should always use a target range of time, bounded at both the beginning and the end. The package execution should begin after the target range end time.

The following example uses two patterns: the insert/update pattern and the delete pattern. A pattern is a generic example that you may use to solve other similar problems. You will work on a table called Lesson36ProductCategorySource. This lesson’s Try It provides the detailed instructions. For now, try to understand each pattern. The insert/update pattern is shown in Figure 36-1. This pattern handles rows that have been inserted or updated in the source during the time range.

Figure 36-1

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.