DTS Programming

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