November 23, 2009 at 9:47 am
Guys,
SSIS package will load data in a table, but before loading I want to enter a record for the load in a Logging Table... Logging Table has fields like filename, job_id, status. Job_id is int and NOT an identity field.... what I need to do is insert a new record in that Logging table with info about the file to be loaded... since other load will be using the same Logging table... what is the best way to insert a record without sharing the job_id? Should I lock the Logging table before I get the Max + 1 as the new job_id and insert a new record or what you suggest?
Job ID will be shared with few vendors who sents more than one files on a day... also there is a always a chance of parallel runs...I was thinking I will create a Proc to insert record in a logging table and call that using Sql Task... but did not want to lock the Logging table..
Trying to get the best way... Thanks!
November 23, 2009 at 10:32 am
I would probably build a sproc that hands out job_id values to use. within it I would start a transaction, lock the record for both reads (as long as NOLOCK isn't used) and writes and then get the new value then write it and clear the lock..
Ok, here.
DECLARE @JobId int
BEGIN TRAN
UPDATE dbo.SomeTable
SET SomeField = SomeField + 0
WHERE SomeOtherField = 'SomeKeyValue'
SELECT @JobId = SomeField + 1
FROM dbo.SomeTable
WHERE SomeOtherField = 'SomeKeyValue'
UPDATE dbo.SomeTable
SET SomeField = @JobId
WHERE SomeOtherField = 'SomeKeyValue'
COMMIT TRAN
SELECT JobId = @JobId
There might be a use of the output clause but I am less sure of the locking aspects of it and I know what the code above does..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply