Post reply

Parallel SSIS tasks failed; sequential worked

  • July 30, 2009 at 1:47 pm

    #140345

    An Integration Services package copied four Oracle tables (version 10.2.0.3) and one flat file into five truncated SQL Server tables. The package then run a stored procedure which updates reporting data tables from these load tables. The package was set up to run all 5 copies in parallel, then, if all succeeded, the stored procedure. It worked when I ran each task individually in BIDS, but when I ran the package under SQL Server Agent it used up to 50% of my quad-processor server for over an hour and appeared to have done little. When I stopped the job the load tables from Oracle were empty.

    I changed the sequence of steps so they ran one step at a time, and the whole package ran in about 90 seconds, as was expected, and worked.

  • July 31, 2009 at 5:15 am

    #1032855

    Alan Spillert (7/30/2009)

    An Integration Services package copied four Oracle tables (version 10.2.0.3) and one flat file into five truncated SQL Server tables. The package then run a stored procedure which updates reporting data tables from these load tables. The package was set up to run all 5 copies in parallel, then, if all succeeded, the stored procedure. It worked when I ran each task individually in BIDS, but when I ran the package under SQL Server Agent it used up to 50% of my quad-processor server for over an hour and appeared to have done little. When I stopped the job the load tables from Oracle were empty.

    I changed the sequence of steps so they ran one step at a time, and the whole package ran in about 90 seconds, as was expected, and worked.

    Check on your server if there is contention for the same resources by these 5 parallel processes. This is the only explanation I could think of.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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

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