.ExceptionFileOptions = 1 ‘DTSExcepFile_SingleFile70
.ExceptionFileRowDelimiter = “{CR}{LF}”
.ExceptionFileTextQualifier = “”
.FetchBufferSize = 1
.FirstRow = 0
.InputGlobalVariableNames = “”
.LastRow = 0
.MaximumErrorCount = 0
.ProgressRowCount = 1000
.SourceSQLStatement = “”
End With
pkg.Tasks.Add tsk
‘Create step for task
Set stp = pkg.Steps.New
With stp
.Name = “stp” & sBaseName
.Description = sBaseName
.TaskName = tsk.Name
End With
pkg.Steps.Add stp
fctCreateDataDrivenQueryTask = stp.Name
Set conSource = Nothing
Set conDest = Nothing
Set tsk = Nothing
Set cus = Nothing
Set stp = Nothing
ProcExit:
Exit Function
ProcErr:
MsgBox Err.Number & “ - “ & Err.Description
GoTo ProcExit

P
ART
II
228
The multiphase data pump option allows you to write code at several different points in the
data transformation process. You can use this option with the Transform Data task, the Data
Driven Query task, and the Parallel Data Pump task.
If you haven’t enabled the multiphase data pump, you can write code for only one point of the
data transformation process—the point at which each row is being transformed. After enabling
this option, you can write code for all these phases and subphases:
•Pre Source Phase—Before the source query is executed.
•Row Transform Phase—Each row of data is processed.
• On Transform Failure—Subphase of the Post Row Transform phase. Occurs when there
is an error in the transformation.
• On Insert Failure—Subphase of the Post Row Transform phase. Occurs when a record
fails to be inserted.
• On Insert Success—Subphase of the Post Row Transform phase. Occurs when a record is
successfully inserted.
• Batch Complete Phase—A batch of records is successfully inserted.
• Post Source Data Phase—The rows have all been processed.
• Pump Complete Phase—The transformation task has completed its work.
Enabling the Multiphase Data Pump
The last section of this chapter explains how to create a multiphase data pump in code. In the
DTS Designer, you can enable the multiphase data pump by doing the following:
1. Right-click on the Data Transformation Services node in the Enterprise Manager.
2. Select Properties from the pop-up menu.
3. Select Show multi-phase pump in DTS Designer on the Package Properties dialog, as
shown in Figure 9.1.
After selecting the multiphase option, you will see a Phases filter on the Transformation tab of
the Transform Data Task Properties dialog, as shown in Figure 9.2. Each transformation can

Even if you remove the multiphase option from the Package Designer, multiple
phases in a transformation will remain. However, you will not be able to view all the
properties of those transformations in the Package Designer without using Disconn-
ected Edit. If any of your transformations do not include a Row Transform phase, you
will not be able to access that phase in the Transform Data Task Properties dialog.
N
OTE
12 0672320118 CH09 11/13/00 5:03 PM Page 229
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DTS Connections and the Data Transformation Tasks
P
ART
II
230
F
IGURE
9.3
You can choose which phases you want to use in the ActiveX Script Transformation Properties dialog.
You can enable the multiphase data pump option in code by using the Application object.
Here’s the VBScript code to do so:
Function Main
Dim app
Set app = CreateObject(“DTS.Application”)
app.DesignerSettings = DTSDesigner_ShowMultiPhaseTransforms
Main = DTSTaskExecResult_Success
End Function
Programmatic Flow with Multiple Phases
Figure 9.4 shows the programmatic flow of the phases and subphases as the Transform Data
task is executed. You can choose to implement one, all, or any combination of these phases and
subphases. When you implement a phase, the specified entry function is called and the code in

9
9
T
HE
M
ULTIPHASE
D
ATA
P
UMP
231
On Insert Failure On Insert Success
OK/SkipFetch Skip Row/Skip Insert
Error/Exception No Error
On Transform
Failure
Row Transform
Batch Complete
Pump Complete
Pre Source
Post Source
Skip Fetch
All Rows Processed Follow Next Row in Batch/Skip Fetch
Follow Next Row after Batch
Post Row
12 0672320118 CH09 11/13/00 5:03 PM Page 231
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
All six properties of the
DTSTransformPhaseInfo
object are read-only. They are as follows:

vt_decimal
data type. When you reference these properties in VBScript, you have to con-
vert them to long integers:
lCurrentSourceRow = CLng(DTSTransformPhaseInfo.CurrentSourceRow)
Listing 9.1 shows a function that uses several of these properties to record the state of the
transformation at the time of an error. You can find this code in a file called UseAllPhases.dts
on the book’s CD.
L
ISTING
9.1
The Properties of the DTSTransformPhaseInfo Object Can Help Determine
What Happened at the Time of an Error
Function fctError
Dim msg, sPhase
sPhase = fctPhaseName
If bDisplayErrMsg Then
msg = “Error in “ & sPhase & vbCrLf
msg = msg & Err.Number & “ “ & Err.Description
msgbox msg
End If
If bRecordErr Then
DTSLookups(“InserttblOrderErrors”).Execute _
DTS Connections and the Data Transformation Tasks
P
ART
II
232
12 0672320118 CH09 11/13/00 5:03 PM Page 232
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DTSSource(“OrderID”), _

Insert Success Read Write(None) OK or AbortPump
Batch Complete Read(None) Read(None) OK or AbortPump
Post Source Data None Write All
Pump Complete Read(None) Read(None) OK or AbortPump
The Multiphase Data Pump
C
HAPTER
9
9
T
HE
M
ULTIPHASE
D
ATA
P
UMP
233
L
ISTING
9.1
Continued
12 0672320118 CH09 11/13/00 5:03 PM Page 233
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Books Online states that the Pre Source phase, the Transform Failure subphase, and the Post
Source Data phase can use all the transformation values that can be returned from a Row
Transform phase.
DTS Connections and the Data Transformation Tasks
P
ART

to
TRUE
in the Pre Source phase. This function calls another function,
fctPhaseName
, which finds the current phase number and converts it into a phase name. These
functions are shown in Listing 9.2.
L
ISTING
9.2
Functions That Display the Current Phase
Function fctPhase
Dim msg
If bDisplayPhase = True Then
msg = fctPhaseName
msgbox msg
End If
End Function
12 0672320118 CH09 11/13/00 5:03 PM Page 234
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Function fctPhaseName
dim sName
Select Case DTSTransformPhaseInfo.CurrentPhase
Case 1
sName = “Pre-Source Data Phase”
Case 2
sName = “Post-Source Data Phase”
Case 4
sName = “Row Transform Phase”
Case 8
sName = “On Transform Failure Phase”

ULTIPHASE
D
ATA
P
UMP
235
L
ISTING
9.2
Continued
12 0672320118 CH09 11/13/00 5:03 PM Page 235
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Listing 9.3 shows the script variable declarations and the code for the Pre Source phase of the
UseAllPhases example. In the Pre Source phase, you have the option of setting the display and
recording options for the task. You can display message boxes that show the current phase, the
transformation progress, or the transformation errors. You can record the progress in
tblOrderProgress and the errors in tblOrderErrors. By default, the message boxes are disabled
and the recording is enabled.
L
ISTING
9.3
The Pre Source Phase from the UseAllPhases Task
Option Explicit
Dim bDisplayProgressMsg, bRecordProgress
Dim bDisplayErrMsg, bRecordErr
Dim bDisplayPhase
Dim lLastInsertSuccessKey
Function PreSourceMain()
‘Set Display and Recording Options
bDisplayProgressMsg = CBool(False)

PreSourceMain = DTSTransformstat_OK
End Function
Row Transform Phase
The Row Transform phase is the default transformation phase. Writing scripts for this phase is
the topic of Chapter 7, “Writing ActiveX Scripts for a Transform Data Task.”
You are required to have a Row Transform phase in at least one of the transformations defined
for a transformation task. All the other phases are optional.
You can modify the values in the transformation’s destination columns in most of the phases.
This is the only phase where you can actually insert rows into the data destination.
Post Row Transform Phase
You cannot write code for the Post Row Transform phase. Instead, you write code for one or
more of the subphases associated with this phase:
• On Transform Failure
• On Insert Failure
• On Insert Success
Zero, one, or two of these subphases will be called for each record being transformed. None of
the subphases will be called if the Row Transform phase is successful but returns a transforma-
tion status of
DTSTranformStat_SkipInsert
or
DTSTransformStat_SkipRow
. On Insert Failure
and On Insert Success are mutually exclusive—one and only one will be called for each
attempted insert into the data destination.
The Multiphase Data Pump
C
HAPTER
9
9
T

Call fctPhase
Call fctError
TransFailureMain = DTSTransformstat_OK
End Function
On Insert Failure Subphase
The Row Transform phase may be completed successfully, but the insertion of a record into the
data destination may fail. The insert failure could be a result of one of the following:
•Violation of a Primary Key constraint
•Violation of a Foreign Key constraint
•Violation of a Unique constraint
•Violation of a Check constraint
DTS Connections and the Data Transformation Tasks
P
ART
II
238
An insert failure is not usually caused by a data type conversion error. Those errors
usually cause a transform failure.
N
OTE
You have read access to the transformation’s source columns and write access to the destina-
tion columns during this phase. It doesn’t do much good to write to these destination columns,
though, because they will be set to Null before the next execution of the Row Transform
phase.
12 0672320118 CH09 11/13/00 5:03 PM Page 238
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The UseAllPhases task also uses this subphase to call
fctError
,as shown in Listing 9.5.
L

D
ATA
P
UMP
239
My testing indicates that this subphase is never executed when Fast Load is used. Fast
Load greatly improves the performance of the Transform Data task. The inability to
use this subphase with Fast Load significantly reduces its value.
This problem only occurs with this subphase—On Transform Failure and On Insert
Success are executed whether or not Fast Load is being used.
C
AUTION
12 0672320118 CH09 11/13/00 5:03 PM Page 239
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
L
ISTING
9.6
The On Insert Success Subphase from the UseAllPhases Task
Function InsertSuccessMain()2
Call fctPhase
‘Save the Value for use in fctProgress.
lLastInsertSuccessKey = DTSDestination(“OrderID”)
InsertSuccessMain = DTSTransformstat_OK
End Function
On Batch Complete Phase
The On Batch Complete phase occurs when a batch is committed to the destination. This phase
is not executed when Fast Load is not being used.
Books Online states that neither the source nor the destination columns are available in this
phase. My testing indicates that they are both available for reading but not writing.
The On Batch Complete phase is useful for keeping track of the progress of a data transforma-

sProgress = “Finished”
End Select
If bDisplayProgressMsg Then
sPhase = fctPhaseName
msg = “Currently in “ & sPhase & vbCRLF
msg = msg & “Progress: “ & sProgress & vbCRLF
msg = msg & “Current Source Row: “ & lCurrentSourceRow & vbCRLF
msg = msg & “Destination Rows Complete: “ & _
lDestRowsComplete & vbCRLF
msgbox msg
End If
If bRecordProgress Then
DTSLookups(“InserttblOrderProgress”).Execute _
lLastInsertSuccessKey, _
sProgress, _
lCurrentSourceRow, _
lDestRowsComplete, _
CLng(DTSTransformPhaseInfo.ErrorRows), _
Now
End If
End Function
Post Source Data Phase
The Post Source Data phase occurs after all the records have been processed. This phase is
executed only once, unless you use the
DTSTranformStat_SkipFetch
transformation value to
execute it repeatedly.
You can access the data in the transformation’s destination columns, but not in the source
columns.
You can use this phase for any final processing that needs to be accomplished on the final row

DTSLookups(“InserttblOrderDates”).Execute _
DTSDestination(“OrderID”), _
DTSDestination(“OrderDate”), _
DTSDestination(“ShippedDate”), _
DTSDestination(“RequiredDate”)
If Err.Number <> 0 Then
Call fctError
End If
PostSourceMain = DTSTransformstat_OK
End Function
DTS Connections and the Data Transformation Tasks
P
ART
II
242
If you specify a value for MaxRows that is less than the number of rows in the data
source, the Post Source Data phase code will not be executed.
C
AUTION
It appears that the final On Batch Insert phase usually occurs before the Post Source
Data phase.
N
OTE
Pump Complete Phase
The Pump Complete phase is the last phase of the data transformation. It executes only once
and cannot be called to execute again.
Books Online states that there is no access to either the source or destination columns from
this phase, but my testing indicates that there is access to both of them.
You would use the Pump Complete phase in much the same way as the Post Source Data
phase. The UseAllPhases task uses this phase to write one final record to report on the

UMP
243
You cannot use Visual Basic to create a custom transformation because the data
pump library has not been made available to Visual Basic.
You can create custom tasks with Visual Basic, though.
N
OTE
See Chapter 32, “Creating a Custom Transformation with VC++,” for more information.
Creating a Multiphase Data Pump in Code
Most of the work in creating a multiphase data pump is writing the ActiveX script code for the
various phases. There are two additional things you have to do when you are creating a task
with phases using Visual Basic code—setting the
TransformPhases
property and setting the
properties that determine the entrance functions.
The
TransformPhases
Property
The
TransformPhases
property is the only extended property for the
Transformation2
object
in SQL Server 2000. This property contains a bitmap that indicates which phases are enabled
for that particular transformation. The values for the
DTSTransformPhaseEnum
constants are as
follows:
•0—
DTSTransformPhase_None

never be called. Also, you will not be able to view this script in the Transform Data
Task Properties dialog because it won’t show up for any of the phase filters.
N
OTE
Setting the Entrance Functions
You have to specify the name of the entrance function for each of the phases that you are
using. This is the function that is called when the programmatic flow reaches the point for the
particular phase. The entrance function also returns a value that determines the result of the
phase.
The entrance functions can be referenced through the
TransformServerProperties
of the
Transformation
object, as shown in Listing 9.10.
L
ISTING
9.10
VBScript Code to Reference the Names of the Entrance Functions
Option Explicit
Function Main
Dim pkg, tsk, cus, trn, trnprp
Set pkg = DTSGlobalVariables.Parent
Set tsk = pkg.Tasks(“tskAllPhases”)
Set cus = tsk.CustomTask
Set trn = cus.Transformations(“trnAllPhases”)
Set trnprp = trn.TransformServerProperties
Msgbox trnprp(“PreSourceDataFunctionEntry”)
Msgbox trnprp(“FunctionEntry”) ‘The Row Transform Phase
12 0672320118 CH09 11/13/00 5:03 PM Page 244
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Continued
12 0672320118 CH09 11/13/00 5:03 PM Page 245
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.