locked
Avoid OLEDB Command use in SSIS ? RRS feed

  • Question

  • How can avoid the Double time iterative OLEDB command in below picture

    First OLEDB command:

    UPDATE Table1
    set INTERNAL_CHART =?
    where INTERNAL_ID =?

    Above LOOKUP2:No Match:

    LName=LName,Fname=Fname --->'U4'

    Second OLEDB command:

    update Table1
    set  bill_status = 'U4', internal_chart=?
    where INTERNAL_ID =?

    For all above, I should update INTERNAL_CHART by INTERNAL_ID

    without Using OLEDB(to avoid Dead Lock issue) .

    Hence I planned to Staging table for  First OLEDB command:(insert the that particular INTERNAL_ID, INTERNAL_CHART) latter update by using SQL Task

    but How Can I do for  Second OLEDB command?

    any one Please help me.

    Thanks


    • Edited by Sign of Shine Friday, May 23, 2014 9:26 AM added image
    Friday, May 23, 2014 9:24 AM

Answers

  • Looking at the picture there shouldn't be a potential for a dead-lock if transactions are not used, furthermore I don't see why not using the OLEDB Command.

    Overall, it might be easier to wrap that all into a stored proc that encompasses a T-SQL Merge command so you don't need the lookups


    Arthur My Blog

    • Proposed as answer by Mike Yin Friday, May 30, 2014 12:50 PM
    • Marked as answer by Mike Yin Monday, June 2, 2014 4:31 PM
    Friday, May 23, 2014 3:48 PM

All replies

  • Looking at the picture there shouldn't be a potential for a dead-lock if transactions are not used, furthermore I don't see why not using the OLEDB Command.

    Overall, it might be easier to wrap that all into a stored proc that encompasses a T-SQL Merge command so you don't need the lookups


    Arthur My Blog

    • Proposed as answer by Mike Yin Friday, May 30, 2014 12:50 PM
    • Marked as answer by Mike Yin Monday, June 2, 2014 4:31 PM
    Friday, May 23, 2014 3:48 PM
  • May I use constraint on that table to avoid Dead lock errors .
    Thursday, June 5, 2014 5:58 AM
  • The more constraints you add the greater the likelihood of a deadlock.

    If you merge the data from one connection you should be fine. Furthermore, you may want to relax the isolation level.


    Arthur My Blog

    Thursday, June 5, 2014 2:05 PM