keeping a track of "import" jobs

[sedgely]

i have a DTS job that takes data from 3 "temporary" tables and inserts it into one of 3 permenant tables (after doing some transorfamtion and verification of the data). The job is scheduled to run daily, what i would like to do is when the job runs for a record to be inserted into a "audit" table detailing the number of records imported the date they were imported and which table imported from and which table inserted into.
I know i could do this with a trigger on each of the permenant tables but this would only work if something was actually inserted and there may be days when no records are imported and on these days i would like to be able to see a "0" entry.
Can anyone point me in the right direction?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JayKusch]

Looks like you need to create your audit table as you described and then as a starting task, execute the needed SELECT Count(*) on the temp tables and set some predefined variables to load into your audit table before the main package is execute. So ... something like ....

declare @Tbl1Name VarChar(100)
declare @Tbl1RecCnt Int

declare @Tbl2Name VarChar(100)
declare @Tbl2RecCnt Int

declare @Tbl3Name VarChar(100)
declare @Tbl3RecCnt Int

delcare @TblLoadDate DateTime

Set @TblLoadDate = GetDate()

SELECT @Tbl1Name = FieldTableName,
@Tbl1RecCnt = Count(*)
WHERE FeildTableName = 'Audit1'

.. Now do something like that for the other 2 tables and then write all your stats to the Audit table like ...

INSERT INTO MyAuditTable (InsertDate, Table1Name, Table1Count, Table2Name, Table2Count, Table3Name, Table3Count)
VALUE (@TblLoadDate, @Tbl1Name, @Tbl1RecCnt, @Tbl2Name, @Tbl2RecCnt,@Tbl3Name, @Tbl3RecCnt)

Hope this helps!







Thanks

J. Kusch

 

[sedgely]

Thanks JayKusch
Will try this in the morning


Cheers, Craig
Si fractum non sit, noli id reficere

 

[sedgely]

jaykusch
could you explain where i should put the code you suggested as i am no SQL expert.
but looking through the code i think i can understand what it is trying to do.
But am not sure how to get the count from each of the temp tables.
To recap...

The three 'temp' tables are:
tbl_staging_NC
tbl_staging_SR
tbl_staging_PRF

The three 'permenant' tables are:
tbl_Final_NC
tbl_Final_SR
tbl_Final_PRF

The audit table is:

tbl_audit

The fields for the audit table are:
dateInserted
insertedInto
numberInserted

Going back to your solution i would need to count the records in each of the temp tables prior to running the transfer job. and insert the results of the count as individual records in tbl_audit to give something like

dateInserted insertedInto numberInserted
01/06/2004 tbl_Final_NC 650
01/06/2004 tbl_Final_SR 1050
01/06/2004 tbl_Final_PRF 25000
02/06/2004 tbl_Final_NC 570
02/06/2004 tbl_Final_SR 1175
02/06/2004 tbl_Final_PRF 27501

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JayKusch]

delcare @NC Int
declare @SR Int
declare @PRF Int

SELECT @NC = Count(*) FROM tbl_staging_NC
SELECT @SR = Count(*) FROM tbl_staging_SR
SELECT @PRF = Count(*) FROM tbl_staging_PRF

Now ... you could insert 3 records into your audit table like so ...

INSERT INTO tbl_Audit
(dateInserted, insertedInto, numberInserted)
VALUE (GetDate(), tbl_FINAL_NC, @NC)

INSERT INTO tbl_Audit
(dateInserted, insertedInto, numberInserted)
VALUE (GetDate(), tbl_FINAL_NC, @SR)

INSERT INTO tbl_Audit
(dateInserted, insertedInto, numberInserted)
VALUE (GetDate(), tbl_FINAL_NC, @PRF)


Thanks

J. Kusch

 

[JayKusch]

OR ... you could expand your tbl_Audit to only hold 1 record per day as in ...

The fields for the audit table WOULD BE:

dateInserted
insertedIntoNC
numberInsertedNC
insertedIntoSR
numberInsertedSR
insertedIntoPRF
numberInsertedPRF

delcare @NC  Int
declare @SR  Int
declare @PRF Int

SELECT @NC  = Count(*) FROM tbl_staging_NC
SELECT @SR  = Count(*) FROM tbl_staging_SR
SELECT @PRF = Count(*) FROM tbl_staging_PRF

Now ... you could insert 3 records into your audit table like so ...

INSERT INTO tbl_Audit 
      (dateInserted, insertedIntoNC, numberInsertedNC)
      (insertedIntoSR,  numberInsertedSR)
      (insertedIntoPRF, numberInsertedPRF)
VALUE (GetDate(), tbl_FINAL_NC, @NC, tbl_FINAL_SR, @SR, tbl_FINAL_PRF, @PRF, )

So your record would then look like ...

01/06/2004 tbl_Final_NC 650 tbl_Final_SR 1050 tbl_Final_PRF 25000

Thanks

J. Kusch