DTS Programming

Remarks

The DTSTransformScriptProperties2 object extends the functionality of the DataPumpTransformScript and inherits the properties and methods of that object. In addition, the extended object supports multiple transformation phases and adds properties to specify the script function entry point for each supported phase.

The following table specifies the transformation phases and the property that specifies the entry point for the phase. For a phase to be supported by a DTSTransformScriptProperties2 object, the function entry point must be specified, and the phase must be specified using the TransformPhases property of the Transformation2 object.

Phase	Description	Entry Point Property
PreSourceData	Occurs before first row is fetched from source connection.	PreSourceDataFunctionEntry
Transform	Occurs after each source row is fetched and before the destination row is written.	FunctionEntry
OnTransformFailure	Occurs after a failure in the Transform phase, indicated by the return of DTSTransformStat_Error or DTSTransformStat_ExceptionRow. Typically this failure is caused by conversion errors.	TransformFailureFunctionEntry
OnInsertSuccess	Occurs after each data row is written successfully to the destination connection.	InsertSuccessFunctionEntry
OnInsertFailure	Occurs after each attempt to write a data row to the destination connection fails (for example, by attempting to write a duplicate value to a primary key field, or a NULL to a NOT NULL field).	InsertFailureFunctionEntry
OnBatchComplete	Occurs in DataPumpTask2 when using the FastLoad option after each batch is written, successfully or unsuccessfully.	BatchCompleteFunctionEntry
PostSourceData	Occurs after the last row is written to the destination connection.	PostSourceDataFunctionEntry
OnPumpComplete	Occurs at the end of the execution of the task.	PumpCompleteFunctionEntry

To create the DTSTransformScriptProperties2 object, declare an object variable or pointer of type DTSTransformScriptProperties2, but use the ProgID of the DataPumpTransformScript object as the parameter for the New method of the Transformations collection. The following Microsoft Visual Basic® code illustrates this:

    Dim objCustTask     As DTS.DataPumpTask2
    Dim objTransform    As DTS.Transformation2
    Dim objTranScript   As DTSPump.DTSTransformScriptProperties2
. . .
    Set objTransform = _
        objCustTask.Transformations.New("DTSPump.DataPumpTransformScript")
    Set objTranScript = objTransform.TransformServer

For more information about when to use the DataPumpTransformScript object instead of the DTSTransformScriptProperties2 object, see Extended DTS Objects.

Example

The following Visual Basic program uses multiphased transformations to insert data into a table and correct certain errors.

The data source is a table named Transactions in a database named DailyRuns. The following is the definition of Transactions:

CREATE TABLE dbo.Transactions (
   CustID INT PRIMARY KEY ,
   CustName VARCHAR (50) NULL ,
   CustAddr VARCHAR (100) NULL ,
   TransAmount VARCHAR (50) NOT NULL )

The data destination is two tables named Transactions and ErrorAmounts in a database named DataPerm. The following are the definitions of these tables:

CREATE TABLE dbo.Transactions (
   CustID INT PRIMARY KEY ,
   CustName VARCHAR (50) NOT NULL ,
   CustAddr VARCHAR (50) NOT NULL ,
   TransAmount MONEY NOT NULL ,
   ErrorCount INT NOT NULL )

CREATE TABLE dbo.ErrorAmounts (
   CustID INT NOT NULL ,
   TransAmount VARCHAR (50) NOT NULL )
The example program inserts the data from DailyRuns..Transactions into DataPerm..Transactions and handles these error conditions:

• If the CustName or CustAddr columns in the source contain Null values, they cannot be inserted into the corresponding columns in the destination, which are specified NOT NULL.
  
  
• An error occurs if the TransAmount column in the source, which is varchar, cannot be converted to money.
  
  
• An error occurs if the destination already contains a row that has the same value for the primary key CustID as the source row.

One or more of these errors can occur in a single source row.

The example uses a DataPumpTransformCopy transformation that supports the Transform phase. The transformation inserts rows from DailyRuns..Transactions into DataPerm..Transactions. A SourceSQLStatement provides an initial value for the ErrorCount column and correctly maps the data source to the destination table.

The example also uses a DTSTransformScriptProperties2 transformation that supports the OnTransformFailure, OnInsertFailure and PreSourceData phases. The transformation processes the errors described above. It contains these ActiveX script functions to support the transformation phases.

Script Function	Transformation Phase
TransformFailed	OnTransformFailure
InsertFailed	OnInsertFailure
InitializeGV	PreSourceData

The TransformFailed function determines if a conversion error to the money data type occurred. If it did, the function opens a Microsoft ActiveX Data Objects (ADO) recordset on the ErrorAmounts table and writes a record containing the CustID and TransAmount columns from the data source. It sets the destination columns TransAmount to 0.0 and ErrorCount to 1. It saves the current source row number in a global variable so the InsertFailed function can determine if TransformFailed found an error converting to money for the current row. If either of the source columns CustName or CustAddr are NULL, it sets the corresponding destination column to "<unknown>".

The InsertFailed function executes when a duplicate primary key error occurs. It opens an ADO recordset and queries for the existing record in the DataPerm..Transactions table. It updates the CustName and CustAddr columns only if the original values in the source row were not NULL. If the TransformFailed function found that an error converting to money occurred, InsertFailed increments the ErrorCount column. Otherwise it adds the TransAmount column from the source row to the TransAmount column of the existing record.

The InitializeGV function initializes the global variable that is used to determine if both a transform failure and an insert failure occurred on the same row.

For more information about these script functions and a view of them separated from their text string, see Phased Transformation Samples.

Note  Database operations using ADO from ActiveX scripts cannot be made to join the package transaction that DTS manages.

Building the Multiphased Example

This is the Visual Basic code for the multi-phased transformation example:

Public Sub Main()
'Copy DailyRuns..Transactions to DataPerm..Transactions.
    Dim objPackage      As DTS.Package2
    Dim objConnect      As DTS.Connection2
    Dim objStep         As DTS.Step2
    Dim objTask         As DTS.Task
    Dim objCustTask     As DTS.DataPumpTask2
    Dim objTransform    As DTS.Transformation2
    Dim objTranScript   As DTSPump.DTSTransformScriptProperties2
    Dim strVBS          As String       'Assemble VBScript text.
    
    Set objPackage = New DTS.Package
    objPackage.FailOnError = True
    
    'Establish connection to source server.
    Set objConnect = objPackage.Connections.New("SQLOLEDB")
    With objConnect
        .ID = 1
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    objPackage.Connections.Add objConnect

    'Establish connection to destination server.
    Set objConnect = objPackage.Connections.New("SQLOLEDB")
    With objConnect
        .ID = 2
        .DataSource = "(local)"
        .UseTrustedConnection = True
    End With
    objPackage.Connections.Add objConnect

    'Create step and task, and link step to task.
    Set objStep = objPackage.Steps.New
    objStep.Name = "CopyNValidateStep"
    Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
    Set objCustTask = objTask.CustomTask
    objCustTask.Name = "CopyNValidateTask"
    objStep.TaskName = objCustTask.Name
    objPackage.Steps.Add objStep
    
    'Link task to connections.
    With objCustTask
        .SourceConnectionID = 1
        .SourceSQLStatement = _
            "SELECT CustID, CustName, CustAddr, TransAmount, 0 AS ErrorCount " & _
            "FROM DailyRuns..Transactions"
        .DestinationConnectionID = 2
        .DestinationObjectName = "[DataPerm].[dbo].[Transactions]"
        .UseFastLoad = False
        .MaximumErrorCount = 4
    End With
    
    'Create transform to copy source to destination data.
    Set objTransform = objCustTask.Transformations. _
            New("DTSPump.DataPumpTransformCopy")
    With objTransform
        .Name = "CopyData"
        .TransformPhases = DTSTransformPhase_Transform
        .TransformFlags = DTSTransformFlag_Default
    End With
    objCustTask.Transformations.Add objTransform
    
    'Create transform to handle error conditions.
    Set objTransform = objCustTask.Transformations. _
            New("DTSPump.DataPumpTransformScript")
    With objTransform
        .Name = "InsertFailure"
        .TransformPhases = DTSTransformPhase_OnInsertFailure + _
            DTSTransformPhase_OnTransformFailure + DTSTransformPhase_PreSourceData
        Set objTranScript = .TransformServer
    End With
    
    'Define the ActiveX script functions.
    With objTranScript
        .InsertFailureFunctionEntry = "InsertFailed"
        .TransformFailureFunctionEntry = "TransformFailed"
        .PreSourceDataFunctionEntry = "InitializeGV"
        .Language = "VBScript"
        strVBS = "Option Explicit" & vbCrLf
        
        strVBS = strVBS & "Function InsertFailed()" & vbCrLf
        strVBS = strVBS & "   Dim rstCustomers " & vbCrLf & "   Dim strConnect" & vbCrLf & "   Dim strQuery" & vbCrLf
        strVBS = strVBS & "   strConnect = ""Provider=SQLOLEDB;Data Source=(local);" & _
                         "Initial Catalog=DataPerm;User Id=sa;Password=; """ & vbCrLf
        strVBS = strVBS & "   strQuery = ""SELECT CustID, CustName, CustAddr, TransAmount, ErrorCount " & _
                         "FROM Transactions WHERE CustID = '""" & vbCrLf
        strVBS = strVBS & "   'open recordset" & vbCrLf
        strVBS = strVBS & "   Set rstCustomers = CreateObject(""ADODB.Recordset"")" & vbCrLf
        strVBS = strVBS & "   rstCustomers.LockType = 3                         'adLockOptimistic" & vbCrLf
        strVBS = strVBS & "   strQuery = strQuery & DTSSource(""CustID"").Value & ""'""" & vbCrLf
        strVBS = strVBS & "   rstCustomers.Open strQuery, strConnect, , , 1             'adCmdText" & vbCrLf
        strVBS = strVBS & "   If rstCustomers.EOF Then" & vbCrLf
        strVBS = strVBS & "      rstCustomers.AddNew" & vbCrLf
        strVBS = strVBS & "      rstCustomers(""CustID"") = DTSSource(""CustID"")" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   If DTSSource(""CustName"") <> ""<unknown>"" Then" & vbCrLf
        strVBS = strVBS & "      rstCustomers(""CustName"") = DTSSource(""CustName"")" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   If DTSSource(""CustAddr"") <> ""<unknown>"" Then" & vbCrLf
        strVBS = strVBS & "      rstCustomers(""CustAddr"") = DTSSource(""CustAddr"")" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   If  CLng(DTSTransformPhaseInfo.CurrentSourceRow) <> (DTSGlobalVariables(""LastErrorRow"")) Then" & vbCrLf
        strVBS = strVBS & "      rstCustomers(""TransAmount"") = rstCustomers(""TransAmount"") + DTSSource(""TransAmount"")" & vbCrLf
        strVBS = strVBS & "   Else" & vbCrLf
        strVBS = strVBS & "      rstCustomers(""ErrorCount"") = rstCustomers(""ErrorCount"") + 1" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   rstCustomers.Update" & vbCrLf
        strVBS = strVBS & "   rstCustomers.Close" & vbCrLf
        strVBS = strVBS & "   InsertFailed = DTSTransformStat_OK" & vbCrLf
        strVBS = strVBS & "End Function" & vbCrLf
        
        strVBS = strVBS & "Function TransformFailed()" & vbCrLf
        strVBS = strVBS & "   Dim rstErrors " & vbCrLf & "   Dim strConnect" & vbCrLf
        strVBS = strVBS & "   DTSDestination(""CustID"") = DTSSource(""CustID"")" & vbCrLf
        strVBS = strVBS & "   DTSDestination(""ErrorCount"") = 0 " & vbCrLf
        strVBS = strVBS & "   On Error Resume Next" & vbCrLf
        strVBS = strVBS & "   DTSDestination(""TransAmount"") = CCur(DTSSource(""TransAmount""))" & vbCrLf
        strVBS = strVBS & "   If Err.Number <> 0 Then " & vbCrLf
        strVBS = strVBS & "      On Error GoTo 0 " & vbCrLf
        strVBS = strVBS & "      strConnect = ""Provider=SQLOLEDB;Data Source=(local);" & _
                                "Initial Catalog=DataPerm;User Id=sa;Password=; """ & vbCrLf
        strVBS = strVBS & "      Set rstErrors = CreateObject(""ADODB.Recordset"")" & vbCrLf
        strVBS = strVBS & "      rstErrors.LockType = 3                         'adLockOptimistic" & vbCrLf
        strVBS = strVBS & "      rstErrors.Open ""ErrorAmounts"", strConnect, , , 2             'adCmdTable" & vbCrLf
        strVBS = strVBS & "      rstErrors.AddNew" & vbCrLf
        strVBS = strVBS & "      rstErrors(""CustID"") = DTSSource(""CustID"")" & vbCrLf
        strVBS = strVBS & "      rstErrors(""TransAmount"") = DTSSource(""TransAmount"")" & vbCrLf
        strVBS = strVBS & "      rstErrors.Update" & vbCrLf
        strVBS = strVBS & "      rstErrors.Close" & vbCrLf
        strVBS = strVBS & "      DTSDestination(""TransAmount"") = 0.0" & vbCrLf
        strVBS = strVBS & "      DTSDestination(""ErrorCount"") = 1" & vbCrLf
        strVBS = strVBS & "      DTSGlobalVariables(""LastErrorRow"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   On Error GoTo 0 " & vbCrLf
        strVBS = strVBS & "   If IsNull(DTSSource(""CustName"").Value) Then" & vbCrLf
        strVBS = strVBS & "      DTSDestination(""CustName"") = ""<unknown>""" & vbCrLf
        strVBS = strVBS & "   Else" & vbCrLf
        strVBS = strVBS & "      DTSDestination(""CustName"") = DTSSource(""CustName"")" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   If IsNull(DTSSource(""CustAddr"")) Then" & vbCrLf
        strVBS = strVBS & "      DTSDestination(""CustAddr"") = ""<unknown>""" & vbCrLf
        strVBS = strVBS & "   Else" & vbCrLf
        strVBS = strVBS & "      DTSDestination(""CustAddr"") = DTSSource(""CustAddr"")" & vbCrLf
        strVBS = strVBS & "   End If" & vbCrLf
        strVBS = strVBS & "   TransformFailed = DTSTransformStat_OK" & vbCrLf
        strVBS = strVBS & "End Function" & vbCrLf
        
        strVBS = strVBS & "Function InitializeGV()" & vbCrLf
        strVBS = strVBS & "   DTSGlobalVariables(""LastErrorRow"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
        strVBS = strVBS & "   InitializeGV = DTSTransformStat_OK" & vbCrLf
        strVBS = strVBS & "End Function" & vbCrLf
        .Text = strVBS
    End With
    objCustTask.Transformations.Add objTransform
    
    'Link task to package, run package.
    objPackage.Tasks.Add objTask
    objPackage.Execute
    Exit Sub
End Sub

See Also

Adding DTS ActiveX Scripts

Adding DTS Column Objects

Adding DTS Transformations

DTS Scripting Reference

New (ID) Method

SourceSQLStatement Property

Transformation2 Object

Transformations Collection

TransformPhases Property