Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Multiphase Data Pump (Field Notes)
By Allan Mitchell
Version 2000
Level Beginner
Page 1 | Page 2 | Page 3 | Page 4 | Page 5

Why the Tutorial?

This is less of a tutorial more a collection of notes hopefully presented coherently about the Multiphase data pump in DTS.

Multiphase data pump what's that?

The Multiphase data pump let's you add a tremendous amount of flexibility to your data pumps in DTS. It allows to operate on the data at different "Phases" within the data pump.

What are the phases I can use?

The phases you can use in the data pump are;

1. Pre Source Phase. This is activated before the whole data pump and therefore only once.

2. Row Transform Phase. This is the actual moving of the data. It's what you would normally see in a data pump.

3. Post Row Transform Phase (On Transform Failure). Caused by errors such as data type conversion errors.

4. Post Row Transform Phase (On Insert Failure). Caused by errors such as Primary key Violations.

5. Post Row Transform Phase (On Insert Success). This fires when the row is valid and moves to the destination.

6. Batch Complete Phase. This will fire for every batch insert you do based on the Insert Batch Size.

7. Post Source Data Phase. Executes after the last row of data has been transformed (or not).

8. Pump Complete Phase. As the name suggests this fires after the data pump completes.

Where can I get it?

Right click on the Data Transformation Services Folder in Enterprise Manager and select Properties. Figure 1.1 shows you where you can enable the Multiphase data pump.

Figure 1.1 Enabling the Multiphase data pump

EnableMPDP
Page 1 | Page 2 | Page 3 | Page 4 | Page 5