locked
Stored procedure for Batch Initialization RRS feed

  • 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

    Tuesday, July 24, 2018 1:17 AM

All replies

  • 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 MSDNFSF@microsoft.com.


    Tuesday, July 24, 2018 7:30 AM
  • 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


    Tuesday, July 24, 2018 8:08 AM
  • 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

    Tuesday, July 24, 2018 3:10 PM
  • 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.


    Tuesday, July 24, 2018 5:50 PM