locked
SSIS multi-threading RRS feed

  • Question

  • I have a vb 2005 app that executes a SSIS package in threads.
    The SSIS package imports a large (20mb) file, does many alternation to it, exports it to the database.
    The SSIS execution is quite frequent and there for multi-threading is needed.

    Here is the issue:
    If i take out the threading everything works great. The second that i add threading, and try to execute a few instances of the SSIS it starts crashing. The thing is that it errors out in different task and is completely unpredictable.

    My question:
    1) Can SSIS be used in a multi-threading environment?
    2) If yes, How do i do it?
    3) Does anyone have any suggestion as to what i should try?

    p.s. Timing is definitely an issue for me.  I got to get this working, tested, approved by 8/27/2006
    ---------
    here are the 3 errors that i'm getting when i turn multi-threading on
    1)Thread "WorkThread0" has exited with error code 0xC0202009.

    2)An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Transaction (Process ID 112) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

    3)The ProcessInput method on component "(i cannot display the name of the task for security reasons" (448) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.


    Wednesday, August 23, 2006 12:00 AM

Answers

  • SSIS has multithreading support built into it. In my experience, it is better to set up your package to leverage this, than to try to force it from an external application. Is there a reason you are trying to do this from outside of SSIS?
    Monday, May 21, 2007 11:23 PM

All replies

  • Hi,

    Was wondering did you ever get a workaround for this.

     

    I am having the same issues.

     

    Any help is appreciated!

     

    Thanks,

    Meena.

    Monday, May 21, 2007 6:43 PM
  • The original error description looks like it is a database locking problem, not an SSIS problem. If you're getting deadlock victim error messages, you need to look at how your package is accessing the database, and how multiple instances of the package executing concurrently are going to interact.

     

    In my experience, it's often easier and simpler to redesign things so that only one instance of the package needs to be running at any given time. Barring this, is it possible to factor the data access so that you're not reading from or writing to the same records from multiple package instances?

     

    Sorry to not be more specific, but without a clearer description of the errors and the types of data access operations that are causing them, it's hard to drill down.

    Monday, May 21, 2007 7:26 PM
  • SSIS has multithreading support built into it. In my experience, it is better to set up your package to leverage this, than to try to force it from an external application. Is there a reason you are trying to do this from outside of SSIS?
    Monday, May 21, 2007 11:23 PM
  •  

    my friend ...

     

    u dont have problem with multi threading

     

    it is problem with records Available there in Table it means you have Primary key on table which will not allow u to copy same row and your execution stops.

     

    you can run as many SSIS tharead as u want...

     

    thx

    Tuesday, April 1, 2008 4:24 PM