Multiple Data Pump tasks

[NH]

Am I missing something about how data pump tasks work?

I created a simple DTS package to split a table into two with one common
column;

e.g.

table_1 (8000 rows)
(col1,col2,col3,col4,col5)

table_a (empty)
(col1,col2,col3)

table_b (empty)
(col1,col4,col5)

I created two connections (sourcedb and destdb)

The then created two data pump tasks between them;
one to transfer col1,col2 and col3 to from table_1 to table_a
and one to transfer col1,col4 and col5 from table_1 to table_b

The result was about 7000 rows went to table_a and 1000 went to table_b and
no single value for col1 exists in both tables..

Of course, creating a second pair of connections and running one data pump
task after another worked fine.. I am just curious as to what happened in the
first instance..

Thanks

NH

[tbradshaw via SQLMonster.com]

NH,

I think you're running into locking situations where two pumps try to execute
in parallel. They are both trying to access the same table at the same time.

Some suggestions: (each represents a complete answer)

(1) Create two pumps as you suggested. You already indicated that this would
work.

(2) Open a pump, in your source tab, choose 'SQL Query' and use a SQL
statement with NOLOCK

(3) In Designer Window, right-click on an empty area & choose 'Package
Properties'. Limit the maximum number of tasks executed in parallel to 1.

Let us know how you make out.

Best Regards,
Tom

Thomas Bradshaw
Data Integration Services
MyWebGrocer LLC

[NH]

You were absolutely right; I tried suggestions 2 and 3, both of which went
without a glitch..

Definately one to remember...

Thanks for your help.

Nick H

Post by tbradshaw via SQLMonster.com
NH,
I think you're running into locking situations where two pumps try to execute
in parallel. They are both trying to access the same table at the same time.
Some suggestions: (each represents a complete answer)
(1) Create two pumps as you suggested. You already indicated that this would
work.
(2) Open a pump, in your source tab, choose 'SQL Query' and use a SQL
statement with NOLOCK
(3) In Designer Window, right-click on an empty area & choose 'Package
Properties'. Limit the maximum number of tasks executed in parallel to 1.
Let us know how you make out.
Best Regards,
Tom
Thomas Bradshaw
Data Integration Services
MyWebGrocer LLC
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200901/1