SSIS - locking tables

  • I am inserting data from a file to different tables and using a  sequence container to rollback the transaction on error. I have insert steps ( 7 SQL tasks) with a sequence container. It is locking the table until  the commit transaction step is executed and the UI users are timing out until then. Is there a better way to achieve the rollback transaction on error?

  • You haven't really provided much in the way of detail.   All we know is that you use an SSIS package, with a container, and that there are 7 SQL tasks.   If you are going to run something that requires a lock on the table, then you are going to have to do it in a time frame when the users are NOT in the system.   Be careful about cancelling long-running tasks that will have to ROLLBACK the transaction, because you could easily find yourself waiting just as long as you have already spent, just to complete the rollback.   This is the kind of thing you generally test before you deploy, to see how long it takes to run, and to see if there's any optimization that can be done to improve run-time.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply