DTS ActiveX Scripts in Visual Basic

DTS ActiveX Scripts in Visual Basic

You assign Microsoft® ActiveX® scripts to a property of objects that require scripts as a single text string. That text string can include embedded carriage return/line feed pairs. If the script string constant contains the " character, represent it as double quotations "", as you would in any Microsoft Visual Basic® string constant. Each scripted object also has properties for the script language and the script function name.

For the ActiveScriptTask, the script text is assigned to the ActiveXScript property. The ScriptLanguage and FunctionName properties are used to specify the scripting language and function entry point.

For the DataPumpTransformScript transformation, the script text is assigned to the Text property. The Language and FunctionEntry properties are used for the script language and function name, respectively.

Example

The following code example assigns a Microsoft Visual Basic Scripting Edition (VBScript) function to a DataPumpTransformScript transformation used by a DataPumpTask2 object:

'Declare the class-specific task, the transformation, and the transform server object.
Dim objDataPump        As DTS.DataPumpTask
Dim objTransformation  As DTS.Transformation
Dim objTransScript     As DTSPump.DataPumpTransformScript

'Create the transformation and the transform server object. Then assign the script string.
'     objDataPump already exists at this point
Set objTransformation = objDataPump.Transformations.New( _
        "DTSPump.DataPumpTransformScript")
Set objTransScript = objTransformation.TransformServer
With objTransScript
    .Text = "Function Main()" & vbCrLf
    .Text = .Text & "    DTSDestination( ""emp_id"" ) = _
        DTSSource( ""emp_id"" )" & vbCrLf
    .Text = .Text & "    DTSDestination( ""Name"" ) = _
        DTSSource( ""lname"" ) & "", "" & DTSSource( ""fname"" )" & vbCrLf
    .Text = .Text & "    DTSDestination( ""JobDesc"" ) = _
        DTSLookups( ""JobDesc"" ).Execute( DTSSource( ""job_id"" ) )" _
        & vbCrLf
    .Text = .Text & "    DTSDestination( ""PubName"" ) = _
        DTSLookups( ""PubName"" ).Execute( DTSSource( ""pub_id"" ) )" _
        & vbCrLf
    .Text = .Text & "    Main = DTSTransformStat_OK" & vbCrLf
    .Text = .Text & "End Function"
    .Language = "VBScript"
    .FunctionEntry = "Main"
End With
. . .
objDataPump.Transformations.Add objTransScript