DTS package - Compare row counts between tables
-
Hello,
I am working on my first DTS package in about 2 years. I am using MS SQL Server 2000.
I have 10 tables and will be importing those tables from 10 text files each night. The files will be imported to tables with temp_ as a prefix. What I need to do is then compare the row counts of the imported (temp_) tables to the ones that are there from the previous import (tables without the temp_ prefix). If the row count between each of the tables is greater than a 10% variance +/- then I need to abort the import and send an email to the client. If the variance is less than 10% for all 10 tables then I need to conitinue with the rest of the DTS which will drop all the tables from the previous import and then rename all the imported tables to remove the temp_ prefix so that they become the new "production" tables. I have it setup to import to the temp_ tables and that is working just fine. I have it setup to drop and rename the tables... what I need help with is how I would go about doing the row count comparison. Can in be done directly in DTS, if so what type of task should I use? Or do I need to do it in a stored procedure called by the DTS? And if I do that (I know how I would do the SP) how would I pass the True or False flag back into the DTS to tell it what to do next? If someone can just get me pointed in the right direction with this it would be great!
Thanks,
Jake
-
In the DTS Task(Assuming that you have a task of type Transform Data Task)in which you are importing the data to temp_ prefixed tables(through Active X Script) all you need to do is add a lookup :
Provide a name and connection for the lookup and then in the query you need to add is select count(*) from tablename
and then in the script you need to add:
If DTSLookups("lookup_name").Execute() > your_count Then
Main = DTSTransformStat_AbortPump
Else
DTSDestination("....") = DTSSource("....")
...........
Main = DTSTransformStat_OK
End If
Hope this helps.
-
No don't add a lookup. The lookup would only be used as part of the datapump and therefore switch it to row-by-row mode which would slow down your entire data load.
Create a stored procedure to perform the comparisson that executes in an ExecuteSQL task after the datapump. At the end of the procedure just put a simple SELECT statement to return a result to DTS.
EG: IF (temp) > (non-temp) by 10% set @errorvalue = 1 ELSE set @errorvalue = 0 SELECT @errorvalue as ErrorValue
Then you assign the result to a global variable and use an ActiveXScript task to determine the variance and send out and Email.
--------------------
Colt 45 - the original point and click interface
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply