Export (0) Print
Expand All

Phased Transformation Samples

SQL Server 2000

Phased Transformation Samples

  New Information - SQL Server 2000 SP3.

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>".


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 
      ' SECURITY NOTE - When possible, use Windows Authentication.
      strConnect = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DataPerm;Trusted_Connection=yes;"
      Set rstErrors = CreateObject("ADODB.Recordset")
      rstErrors.LockType = 3                            'adLockOptimistic
      rstErrors.Open "ErrorAmounts", strConnect, , , 2  'adCmdTable
      rstErrors("CustID") = DTSSource("CustID")
      rstErrors("TransAmount") = DTSSource("TransAmount")

      'Indicate error in destination table, and flag that that transform error occurred in this row.
      DTSDestination("TransAmount") = 0.0
      DTSDestination("ErrorCount") = 1
      DTSGlobalVariables("LastErrorRow") = _
   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>"
      DTSDestination("CustName") = DTSSource("CustName")
   End If
   If IsNull(DTSSource("CustAddr")) Then
      DTSDestination("CustAddr") = "<unknown>"
      DTSDestination("CustAddr") = DTSSource("CustAddr")
   End If

   TransformFailed = DTSTransformStat_OK
End Function
© 2014 Microsoft