September 12, 2005 at 1:10 am
We have a DTS package that imports files from different Lenders as they arrive. The files don’t come every day; only when the Lenders have enough data to send us one. For example,
Monday:
Lender1 file
Lender3
Tuesday:
(No files at all)
Wednesday
Lender 2
Thursday
Lender1
Lender 4
Etc..
We import the files into our database daily. Once the files are imported, if a user finds a record with an incorrect address or zipcode they can manually correct that field. It is also possible, I am told, that we have imported Lender 1’s file from Monday and Thursday only to be told the following week that last Monday’s Lender 1 import had some bad data and needs to be rolled back! The obvious difficulty is that a user may have changed the data so if we roll back, we loose their changes.
I’m looking for a way to handle this problem. I’m wonder if sql 2005 “snapshot” could be relevant. Any ideas appreciated.
TIA,
Bill
P.S. If you think the question should go in another forum, please advise.
September 12, 2005 at 2:52 pm
When you find out that a lender's import had bad data, do you have to back out all the data that was imported from that file or only specific rows?
Maybe a transaction log auditing tool like Log Explorer would help?
Greg
Greg
September 12, 2005 at 11:22 pm
I'd be inclined to back out all the data from that Lender run for that day. The errors typically are "all date fields blank" as opposed to a bad record, But you definitiely raise an interesting point that I need to check into. I think that DTS regards a file import as one transaction and maybe Lumigent would have something to offer.
Thanks,
Bill
September 13, 2005 at 3:30 am
It sounds like you are trying to use the database technology to overcome an application problem.
It may well be possible to use a log sniffer (e.g. Log PI, etc) to generate undo SQL for the faulty data file. You can use the same product to identify post-import changes and generate the SQL to re-apply them.
However, it is important to consider the application issues. Presumably the application contains some form of audit log to say file x has been received and loaded. If you backout the file without updating the audit log, then the integrity of the application becomes suspect, and may not meet regulatory or business needs.
Also, is each input file completely stand-alone, or is the data cumulative? If file 2 says that Fred has finished payments on his loan and you replace the details for Fred with file 1, your system may hold a permanent record that Fred still owes money.
Although the technology can help, the primary focus on fixing this problem should be with the application design and the business requirements that drive that design.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 13, 2005 at 4:52 am
I agree you have a design flaw. There should be a unique identifier used to key off of included in the input file and db. Without it you are at the mercy of the current market tools designed to help..
Workarounds usually cause more time and money to do than actually fixing the design flaw.
Just my thoughts.
September 13, 2005 at 7:21 pm
All excellent thoughts! Indeed I think the app has a flaw also and am discussing the matter with the programmers\analysts.
Thanks,
Bill
September 14, 2005 at 8:43 am
Would it be possible to fix the data, rather than back it out & reload it ? When your "fixed" data comes in, join it to your live table and update it from there.
September 15, 2005 at 1:24 am
Veteran,
A simple yet powerful idea! Hadn't thought of it at all.
Thanks,
Bill
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply