October 17, 2011 at 12:27 pm
Just wondering what are the recommended techniques to determining if a file has been previously loaded via SSIS?
If a file is supplied (flat file, excel...) that happens to have the same filename, how would you assess if the contents of the file were previously loaded? I've seen some techniques suggesting hash or checksum tracking. Don't quite understand those techniques, although they seem promising.
Thanks in advance for any direct advice or pointers to useful articles.
--Pete
October 17, 2011 at 12:39 pm
I would store the filename as a last step in the load process and do the load process as transactionally as possible. Then at the top of the process I would check the filename against the list and use a precedence constraint and expression to halt execution of the rest of the process if it had already been processed. Also I like to either move the file to another location.
CEWII
October 18, 2011 at 10:23 am
Elliott Whitlow (10/17/2011)
I would store the filename as a last step in the load process and do the load process as transactionally as possible. Then at the top of the process I would check the filename against the list and use a precedence constraint and expression to halt execution of the rest of the process if it had already been processed. Also I like to either move the file to another location.CEWII
Elliott,
The OP mentioned that the file name could/would be the same, so storing the file name alone when processed wouldn't work. You could do the file name and a processed date and then check the file name and created date on the new file to see if it is AFTER the max processed date.
Having said that I think making the last step an archive and/or rename of the processed file is the way to go. Then you know it hasn't been processed because it is there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 18, 2011 at 10:32 am
ah, missed that.. I would rename the file at the end and/or move it.
CEWII
October 18, 2011 at 10:32 am
Having said that I think making the last step an archive and/or rename of the processed file is the way to go. Then you know it hasn't been processed because it is there.
+1 to that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 18, 2011 at 10:43 am
Elliott Whitlow (10/18/2011)
ah, missed that.. I would rename the file at the end and/or move it.CEWII
I figured you missed it because I've seen your posts and know you know what you are doing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply