Best approach for history retention in SSIS ETL

robertfl77
robertfl77 used Ask the Experts™
on
I am looking for advise what would be the best approach for storing history of ETL load. The way ETL works is I am receiving flat file and it is loaded to staging area and from staging area to a production table and to a view
What would be the best approach in case I got duplicate file and load it I do not want that to happen
Thanks
Robert
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
If you want to prevent a duplicate file load, you need a unique way to identify the file.  How would you know it's a duplicate?  Does the file have a unique name?  Or time stamp?  Or column with a unique id ?

Author

Commented:
Actually there is no unique field in a data that is being provided, execept filename
Top Expert 2010
Commented:
If the filename is unique, you can track previously loaded filenames before inserting the new file.  So, for example, add a column to your table for the filename.  Before you load a new file, check and see if any records exist with the filename equal to the filename you're about to load.  If you find at least one, you know the file has been previously loaded and you should not load it again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial