Phased Transformation Samples
These Microsoft® Visual Basic® Scripting Edition (VBScript) functions support a Data Transformation Services (DTS) package program that uses multiphase transformations. For more information, see DTSTransformScriptProperties2 Object.
TransformFailed Function
If an error occurred converting to money, TransformFailed opens a Microsoft ActiveX® Data Objects (ADO) recordset on an error records table. Then it writes a record containing the primary key from the data source and the invalid money field. It sets destination columns to indicate the error occurred. It saves the current source row number in a global variable to indicate the conversion error occurred for the current row. If source columns are Null, it sets the corresponding destination column to "<unknown>".
Example
The following is the VBScript for the TransformFailed function:
Function TransformFailed()
'Called on transform failure, usually conversion error or Null -> NOT NULL error.
Dim rstErrors
Dim strConnect
DTSDestination("CustID") = DTSSource("CustID")
DTSDestination("ErrorCount") = 0
'See if transaction amount conversion error occurred.
On Error Resume Next
DTSDestination("TransAmount") = CCur(DTSSource("TransAmount"))
'Conversion error occurred. Write bad transaction amount to error table.
If Err.Number <> 0 Then
On Error GoTo 0
strConnect = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DataPerm;User Id=sa;Password=; "
Set rstErrors = CreateObject("ADODB.Recordset")
rstErrors.LockType = 3 'adLockOptimistic
rstErrors.Open "ErrorAmounts", strConnect, , , 2 'adCmdTable
rstErrors.AddNew
rstErrors("CustID") = DTSSource("CustID")
rstErrors("TransAmount") = DTSSource("TransAmount")
rstErrors.Update
rstErrors.Close
'Indicate error in destination table, and flag that that transform error occurred in this row.
DTSDestination("TransAmount") = 0.0
DTSDestination("ErrorCount") = 1
DTSGlobalVariables("LastErrorRow") = _
CLng(DTSTransformPhaseInfo.CurrentSourceRow)
End If
On Error GoTo 0
'If NULL is in Name or Address, write <unknown>. Otherwise update field.
If IsNull(DTSSource("CustName").Value) Then
DTSDestination("CustName") = "<unknown>"
Else
DTSDestination("CustName") = DTSSource("CustName")
End If
If IsNull(DTSSource("CustAddr")) Then
DTSDestination("CustAddr") = "<unknown>"
Else
DTSDestination("CustAddr") = DTSSource("CustAddr")
End If
TransformFailed = DTSTransformStat_OK
End Function
InsertFailedFunction
InsertFailed executes when a duplicate primary key error occurs. It opens an ADO recordset and queries for the existing record in the destination table. It updates columns only if the original values in the source row had not been NULL. If an error converting to money had occurred, it increments an error column. Otherwise, the function adds the TransactionAmounts column from the source row to the corresponding column of the existing record.
Example
he following is the VBScript for the InsertFailed function:
Function InsertFailed()
'Called when insert fails, usually because the primary key is already present in destination.
Dim rstCustomers
Dim strConnect
Dim strQuery
'Open recordset on row already present in destination table.
strConnect = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DataPerm;User Id=sa;Password=; "
strQuery = "SELECT CustID, CustName, CustAddr, TransAmount, ErrorCount FROM Transactions WHERE CustID = '"
Set rstCustomers = CreateObject("ADODB.Recordset")
strQuery = strQuery & DTSSource("CustID").Value & "'"
rstCustomers.LockType = 3 'adLockOptimistic
rstCustomers.Open strQuery, strConnect, , , 1 'adCmdText
'Add new row if source primary key is not already present.
If rstCustomers.EOF Then
rstCustomers.AddNew
rstCustomers("CustID") = DTSSource("CustID")
End If
'If name or address not NULL in source, update destination.
If DTSSource("CustName") <> "<unknown>" Then
rstCustomers("CustName") = DTSSource("CustName")
End If
If DTSSource("CustAddr") <> "<unknown>" Then
rstCustomers("CustAddr") = DTSSource("CustAddr")
End If
'If no transform failure occurred, add to transaction amount.
If CLng(DTSTransformPhaseInfo.CurrentSourceRow) <> (DTSGlobalVariables("LastErrorRow")) Then
rstCustomers("TransAmount") = rstCustomers("TransAmount") + DTSSource("TransAmount")
'If transform failed in this row, increment error count.
Else
rstCustomers("ErrorCount") = rstCustomers("ErrorCount") + 1
End If
'Update and close recordset.
rstCustomers.Update
rstCustomers.Close
InsertFailed = DTSTransformStat_OK
End Function
InitializeGV Function
InitializeGV initializes a global variable.
Example
The following is the VBScript for the InitializeGV function:
Function InitializeGV()
'Write row 0 into transform error row indicator.
DTSGlobalVariables("LastErrorRow") = _
CLng(DTSTransformPhaseInfo.CurrentSourceRow)
InitializeGV = DTSTransformStat_OK
End Function