I don't know if I can explain what I am needing well, but here goes. The regular DBA is out on a medical leave and I am filling in or trying to that is. We are using a Repository package to pull data into a table nightly from a text file. I need a way to verify that (for example) the dollar amounts and units in the table match what is in the text file and if they do not match have an alert emailed to me. Any help is appreciated. JP
If you suspect that the package is going to import the wrong values into the database, the first line of defence would be to test and thoroughly debug the package rather than to wait for errors and alerts. To be able to compare the text file with the imported values you will have to create a procedure to read the text file again row by row comparing the result with every matching row in the table and then trigger the execution of an alert. I would not encourage this - There is a high chance that you might send the regular DBA on permanent medical with a STROKE. Nathan H.O. Moderator SQL-Server-Performance.com
U can design a DTS package for this... If its worth the effort [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1">
The issue is that we have never had a problem with the fields not matching, only a problem with the text file not copying over from our Unix system correctly 1st before it is imported into the table, however my boss now wants this verification in place for some reason, so I don't have much say in the matter. I only want to compare 3 rows, and I just want to be alerted when the totals don't match (if that ever happens).
After importing the file into database, run a shell script. 1. BCP out the imported data into a text file. 2. Comapre the files using 'comp'. 3. Redirect the output to a file. 4. Import the file into database to parse or use some command line utility for the same. 5. If difference is found, generate alert. HTH. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
> The issue is that we have never had a problem with the fields not matching, only a problem with > the text file not copying over from our Unix system correctly 1st before it is imported into the > table If so, you should varify the file copy before importing it into the database. Otherwhise to what would you compare it? I mean, if the file was not copied correctly, you will import incorrect values. And no matter what way you use to compare the imported data with the data in the file, it will match, wouldn't it? (as long as you read them correctly) Or am I missing something... Bambola.
Right Bambola... great catch. I thought the requirement was to see if the file was imported correctly intead of understanding if correct file was imported. So much for shifting just one word... Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
