Stored procedure for Batch Initialization

Question

Hi guys,

I have a package that I want to call two stored procedure from.

The first one initialize the ssis batch. However, I want this stored procedure to lock my batch table so that no other process can query the table.

Then the second patch completes the ssis batch. And I want this task to unlock the batch table after each successful run.

---

me

Answer 1

Hi liluchay,

Assume you are using Execute SQL Task to call the stored procedure in SSIS.

As far as I know, the Execute SQL Task does not have any properties to lock table. It's available in OLE DB Destination.

So, I would suggest you lock table or unlock table directly in stored procedure itself.

SQL Server - How to lock a table until a stored procedure finishes

Check if this helps.

Regards,

Pirlo Zhang 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Can I ask reason for doing this?

Why not keep all required logics within same procedure in that case?

Locking table like this inside SSIS cause issues especially when there are other tasks accessing it and doing some distributed transaction logic

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

Hi Visakh,

I have an execution log table that logs the status of my package execution. Showing: Successful, Failed and Pending.

However, I need a way to know whether the status showing pending because:

1. the package is currently ran by another process/computer.

2. or if the package failed unexpected (like a system crash) and was unable to update my execution log.

---

me

Answer 4

In the case of a hard system crash, nothing is going to help.

Assuming you only run one process at a time, you can simply add a first step to reset any status of "processing" to "failed".

If you are running multiple it gets complicated.  You would have to have a timeout of some kind to say, nothing has updated this log in 2 hours, it must be dead.