Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GhantaBro
Posting Yak Master
215 Posts |
 Posted - 2009-11-23 : 11:27:11
|
| 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? Trying to get the best way... Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-23 : 11:31:54
|
| why cant you make jobid identity? also is there a chance of parallel loading of table? |
 |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-11-23 : 11:34:14
|
| 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...thanks for the quick response... 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.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-23 : 11:36:57
|
| if parallel loading happens then you need to have locking for them not to get same job id |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-11-23 : 11:37:35
|
| thank you! |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-11-23 : 12:05:50
|
| He Visakh something like this should work fine rite...BEGIN TRAN SELECT TOP 1 * from [dbo].Logging_Table WITH (TABLOCKX, HOLDLOCK) WHERE 1 = 2 SELECT @Job_ID = ISNULL (MAX (job_id), 0) + 1 FROM [dbo].Logging_TableInsert into [dbo].Logging_Table (job_id, file_name, .....)Select @Job_ID, ....................IF @@TRANCOUNT > 0 COMMIT TRAN Thanks! |
|
|
|
|
|
|
Need advice on control table