Identity Column as PK or Unique Constraint (int column)
This column allowed us to see if a row in the source was new or already existed by comparing the source ID value with those that exist in the destination.
LastUpdateDate (DateTime Column)
This column was automatically updated by our application. If we found that our row was an existing row (based on the identity column value above) we were able to compare the source LastUpdateDate column with the DestinationUpdateDate column to see if the row's data changed since the last extraction.
To make things easier for us, we created a table that held the current Id value and current update date values for each table we extracted. This allowed for easy comparison during each load and it turned out that this table was also heavily reported against. (Reports contained the extract datetime so report viewers could clearly see how current the data was.)
If you do not have the luxury of having these columns present in your source you may have to key off of the source table's primary key. You may also have to perform comparisons of the columns that can potentially change.
Dig Deeper on SQL Server Interoperability
Related Q&A from Joe Toscano
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ... Continue Reading
Is there a query to search text in a SQL Server DTS package? SQL Server expert Joe Toscano explains which database objects can be viewed. Continue Reading
Getting an error when importing data from an Excel spreadsheet to SSIS in SQL Server 2005? Try lining up data types in Excel and SSIS so they match. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.