SSIS and transactions

Last Post 16 Jan 2012 10:01 AM by gunneyk. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
dstoltz
New Member
New Member

--
11 Jan 2012 03:15 AM


I have an SSIS package that truncates a table (we can call it TABLE1), runs a SQL query, and then repo***tes TABLE1 with "fresh" data from another source...this occurs every minute.

In the background, there are processes that query this table, and occassionally grab the table data immediately after the truncation, resulting in an empty query result...

Is there a way I can "wrap" the SSIS package in a transaction that would essentially block queries to this table until the package is done running, and the data is re-po***ted?

Any help appreciated.

gunneyk
New Member
New Member

--
11 Jan 2012 02:24 PM
Sure. Issue a BEGIN TRAN before the TRUNCATE and then a COMMIT TRAN or ROLLBACK TRAN when you are done po***tng the table.
dstoltz
New Member
New Member

--
12 Jan 2012 07:26 AM
I'm not quite sure how to do this, since the truncate query is in the intial "execute SQL task", followed by another task, and then at the end there is a "data flow task" which actually pulls the data into the table...

Would I do this by adding an "execute SQL task" to the very beginning with the "BEGIN TRAN", and then adding another "execute SQL task" at the very end with "COMMIT TRAN" ? Will it "know" this is the same transaction if it's in different parts of the flow? (sorry, kinda new to SSIS)

Not sure if that is the correct way to proceed.

Not sure why this forum thinks the word "p o p u l a t e" is a bad word, but it's masking it in my posts....
gunneyk
New Member
New Member

--
12 Jan 2012 09:22 AM
There is a property in the connection manager called RetainSameConnection that if true should allow all the tasks to share the same connection. That way if you issue a BEGIN TRAN and a TRUNCATE in the first Execute SQL task the lock should be held for the duration of the other tasks. You will just have to remember to issue a COMMIT or ROLLBACK after the data flow as appropriate.
dstoltz
New Member
New Member

--
12 Jan 2012 09:28 AM
Awesome information - thank you! I will check it out.
dstoltz
New Member
New Member

--
16 Jan 2012 05:27 AM
Wow gunneyk,

This worked perfectly - thank you.
gunneyk
New Member
New Member

--
16 Jan 2012 10:01 AM
Glad to hear. You are most welcome.
You are not authorized to post a reply.

Acceptable Use Policy