Using ActiveX Scripts in DTS

In Data Transformation Services (DTS), you can extend the capabilities of your DTS package by using Microsoft® ActiveX® scripts that implement the objects, properties, methods, and collections of the DTS object model. Using ActiveX scripts, you can:

• Format and transform the data as it is copied from its source to its destination.
  
  
• Write functions that use conditional logic to manage package workflow or that process data on a row-by-row basis.
  
  
• Create, use, and modify the values stored in DTS global variables.
  
  
• Manipulate COM objects built for data access and utility functions.
  
  
• Create and use Microsoft ActiveX Data Objects (ADO) connections, commands, recordsets, and other objects to access and manipulate data.

Microsoft Visual Basic® Scripting Edition (VBScript) and Microsoft JScript® are available with an installation of Microsoft SQL Server™. If you plan to write ActiveX scripts in a language other than VBScript and JScript, be sure the language library for the scripting language you use is installed. For more information, search on "VBScript" and "JScript" in the MSDN® Library at Microsoft Web site.

Writing ActiveX Scripts in DTS

In DTS, you can write the following types of scripts:

• Transformation scripts that are applied to data on a row-by-row basis. The script executes each time a row of data is read from the source.

  You add these scripts only to DTS tasks that transform data: the Transform Data task; the Data Driven Query task; and the Parallel Data Pump task (available only programmatically). These tasks use the data pump to transform the data.

• ActiveX scripts that function as tasks. The script is run once each time the task is called by the package.

  You can use an ActiveX script task to perform functions that are not available in the other tasks in DTS Designer. For more information, see ActiveX Script Task.

• Workflow scripts that are applied to a package step prior to step execution. The script is run once each time the task is called by the package.

  You can use an ActiveX workflow step script to customize step execution. For example, you can use certain return codes in an ActiveX workflow step script to prevent other tasks in a package from executing, or you can allow a task to execute or mark the task as waiting to be run. For more information about writing scripts that control workflow, see DTS Package Workflow.

Generally, you do not use an ActiveX script task or ActiveX workflow script to operate on data on a row-by-row basis because it is less efficient than using a transformation script. However, it is possible to do so. For example, you could use an ActiveX Script task to create one or more ADO connections and populate a set of text files with data from an ADO recordset.

Important  An ActiveX script in a transformation can affect the execution speed of a DTS package. Therefore, if performance is a priority, use scripting carefully when building a package. For more information, see Enhancing Performance of DTS Packages.

Adding ActiveX Scripts to a DTS Package

You can add scripts to a package in DTS Designer, in the DTS Import/Export Wizard, or programmatically. For more information about adding scripts programmatically, see Adding DTS ActiveX Scripts.

To add transformation scripts:

• In DTS Designer, add transformation scripts in the ActiveX Script Transformation Properties dialog box to define an ActiveX Script transformation for a Transform Data task or a Data Driven Query task.
  
  
• In the DTS Import/Export Wizard, add transformation scripts in the Transformation tab of the Column Mappings and Transformations dialog box. For more information, see Creating a DTS Package with the DTS Import/Export Wizard.

To add ActiveX Script Tasks:

• In DTS Designer, drag an ActiveX Script Task onto the design sheet and add the script when configuring the task.

To add ActiveX workflow scripts:

• In DTS Designer, access the Workflow Properties dialog box associated with a package step.

Scripting Capabilities

The range of functionality you can access from ActiveX scripts covers:

• The SQL Server environment. You can use Transact-SQL statements in your scripts and access SQL Server tables.
  
  
• Data access interfaces. SQL Server installations include familiar data access interfaces such as ADO, Data Access Objects (DAO), and Remote Data Objects (RDO), which you can use in your scripts to make connections, create recordsets, and execute SQL commands.
  
  
• Custom COM objects. You can access custom COM objects you develop in your scripts, if the objects are available on the server running the package.
  
  
• The scripting language. You can use any function of the scripting language you code with, provided the scripting engine for the language is installed on your server. Most scripting languages allow you to:
  • Use looping and conditional logic.
    
    
  • Write functions that control row selection, determine workflow, control the success or failure of an operation, or throw exceptions. These determinations are made by specialized return code constants specified by the DTS object model.

  For example, a scripting language such as VBScript allows you to use intrinsic functions supplied by the language, such as Trim, Len, and CInt, and validate data in a field with functions such as IsNumeric or IsDate. If you require functionality beyond what can be achieved using a scripting language, you may want to program your own DTS applications or custom tasks. For more information, see Creating DTS Packages in Visual Basic.

Scripting Examples

The following DTS ActiveX script examples show:

• ActiveX Script transformations that transform date data, concatenate columns, and validate data.
  
  
• ActiveX Script tasks that connect and use ADO objects, and populate global variables.

Simple Column Transformation

You have a daily sales table sent to your Accounting department. However, your accounting month is not based on a calendar month. Sales before the 15th day of the month are considered sales for that month. Any sales that take place on the 15th or after are considered sales for the following accounting month.

The following ActiveX Script transformation, written in VBScript, copies all the columns from the source to destination table, for each row of source data, except for the AccountingMonth column. For that column, the Sale_Date is checked to see if the sale took place before or after the 15th, and the AccountingMonth adjusted accordingly.

Function Main()
   DTSDestination("DeptName") = DTSSource("DeptName")
   DTSDestination("Sales") = DTSSource("Sales")
   DTSDestination("Sale_Date") = DTSSource("Sale_Date")
   
' break the day out of the Sale_Date
   theDay = Day(DTSSource("Sale_Date"))

' if the day field is before the 15th, the Accounting Month is the same as
' the current Sale_Date month
   If theDay < 15 then
      DTSDestination("AccountingMonth") = DTSSource("AccountingMonth")
   Else
' if the day is the 15th or later, the sales dollars belong in the next
' Accounting Month 
      theMonth = DTSSource("AccountingMonth") + 1
      ' check if we were in December when we added 1 to the month, and       ' roll it to January 
      If  theMonth > 12 then
         theMonth = 1
      End if
      DTSDestination("AccountingMonth") = theMonth
   End If

   Main = DTSTransformStat_OK

End Function

Concatenating Columns

In the following example, an ActiveX Script transformation, written in VBScript, consolidates the Sales_Month, Sales_Day, and Sales_Year columns from the source table into a single Sales_Date column in the destination table. This script is run on each row in the source data, and can be used in those tasks that operate on a row-by-row basis.

Function Main()
   DTSDestination("CustomerID") = DTSSource("CustomerID")
   DTSDestination("Sales_Date") = Trim(DTSSource("Sales_Month")) _
       + "/" + Trim(DTSSource("Sales_Day")) + "/" +  _
      Trim(DTSSource("Sales_Year"))
   Main = DTSTransformStat_OK
End Function

Transforming Date Data

When importing data from a file to an OLE DB destination table, you can use the VBScript CDate function to convert date data if the date format is in a text or character field and is not in the format required by OLE DB, which is yyyy-mm-dd hh:mm:ss:sss. CDate is useful when the source data is in more than one format. If the source data is in a single format, then consider using the Date Time transformation, which is faster.

Function Main()

   DTSDestination("Total Sales") = DTSSource("Total Sales ")
   DTSDestination("DestColumnDate") = CDATE(DTSSource("SourceColumnDate"))

   Main = DTSTransformStat_OK

End Function

Reading Values from a Text File Using FileSystemObject

In the following VBScript example, the input text file, Start_End_Dates.txt, contains the start and end dates to be read into global variables. The text file is stored on the C:\ drive. The start date is the first line of text and contains "01/01/00" and the second line contains the end date, which is "01/31/00". After the package executes and the script runs, two message boxes are displayed. The first message box shows "The Start Date is: 01/01/00", and the second message box shows "The End Date is: 01/31/00".

' Read start and end dates from a flat file and 
' store the values in dynamically generated global variables
'Function Main()

    dim oFSO
    dim x

'  instantiate the Scripting Object
    set oFSO = CreateObject("Scripting.FileSystemObject")

'   Open the file
    set x = oFSO.OpenTextFile("C:\Start_End_Dates.txt")

'  store the first line, which is the Start Date, in a global variable
    DTSGlobalVariables("StartDate").value = x.Readline
    MsgBox "The Start Date is: " & DTSGlobalVariables("StartDate").value

'  store the second line, which is the End Date, in a global variable
    DTSGlobalVariables("EndDate").value = x.Readline
    MsgBox "The End Date is: " & DTSGlobalVariables("EndDate").value

    x.Close

    Main = DTSTaskExecResult_Success

End Function

Using an ADO Connection and Recordset to Check Records

The following ActiveX script creates a connection to the Northwind database and the employee table and counts the number of employee records. If employee records are found in the table, the script displays the number of employees and sends a success flag back to the package. Otherwise, the script sends a failure flag. Those flags can be used to trigger other tasks. For example, the success flag can signal that the table has records and then execute a Bulk Insert task. You can use the failure flag to execute a Send Mail task informing a database administrator (DBA) that a potential problem exists.

dim myConn
dim myRecordset
dim iRowCount

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties to point to the Northwind database,
' using the Customers table
myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); _
   Initial Catalog=Northwind;user id = 'sa';password=''"

mySQLCmdText = "Select 'rowcount' = Count(*) from Customers"

myRecordset.Open mySQLCmdText, myConn

set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")

If iRowCount.Value = 0 then
   Main = DTSTaskExecResult_Failure
Else
   MsgBox "The number of customers is: " & iRowCount.Value
   Main = DTSTaskExecResult_Success
End If

Inserting Rows into a Table using an ADO Recordset

The following example contains an ActiveX script written in VBScript that shows how to connect to a source using ADO and how to insert the rows into the destination table. The example uses tables from the Northwind database, and one that you must create, a NewEmployeeTerritory table that contains the EmployeeID and the new TerritoryID that the employee is assigned to. These new territory assignments need to be entered into the EmployeeTerritory table.

To run this example, do the following:

1. Create a table named NewEmployeeTerritory in the Northwind database that has a schema identical to the EmployeeTerritory table.
   
   
2. Insert the following four records into the NewEmployeeTerritory table:

   EmployeeID    TerritoryID

   1             03801
   1             07960
   3             40222
   9             11747

3. Create a new DTS package in DTS Designer.
   
   
4. Drag an ActiveX Script task onto the design sheet.
   
   
5. In the ActiveX script box, place the following code between the FUNCTION MAIN() and END FUNCTION statements:

   ' These values were copied from the ADOVBS.INC file.
   '---- CursorTypeEnum Values ----
   Const adOpenForwardOnly = 0
   Const adOpenKeyset = 1
   Const adOpenDynamic = 2
   Const adOpenStatic = 3
   
   '---- CommandTypeEnum Values ----
   Const adCmdUnknown = &H0008
   Const adCmdText = &H0001
   Const adCmdTable = &H0002
   Const adCmdStoredProc = &H0004
   
   dim countr
   
   ' Instantiate the ADO objects.
   set mySourceConn = CreateObject("ADODB.Connection")
   set mySourceRecordset = CreateObject("ADODB.Recordset")
   
   'Set the connection properties to point to Northwind. 
   'Use the NewEmployeeTerritories table.
   mySourceConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); _
       Initial Catalog=Northwind;user id = 'sa';password=''"
   
   mySQLCmdText = "Select * from NewEmployeeTerritories"
   
   'Execute the mySQLCmdText, and put the data into the myRecordset object. 
   mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeyset
   
   If mySourceRecordset.RecordCount < 1 Then
       MsgBox " There are no records found. Return a Failure code"
       Main = DTSTaskExecResult_Failure
   Else
   ' Since we have records to insert into the EmployeeTerritory table, create
   ' a Connection object and do the INSERT.
       dim EmpID, TerrID, myDestSQL
       set myDestConn = CreateObject("ADODB.Connection")
       myDestConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); _
       Initial Catalog=Northwind;user id = 'sa'"
       
       for countr = 1 to mySourceRecordset.RecordCount
           EmpID = mySourceRecordset.Fields("EmployeeID").value
           TerrID = mySourceRecordset.Fields("TerritoryID").value
           ' Put single quotes around the TerrID since it is a varchar and         'needs to have the single quotes when it is in the VALUES list.
           TerrID = "'" & Terrid & "'"
   
           myDestSQL = "INSERT INTO EmployeeTerritories _
           VALUES ( " & EmpID & ","  & Terrid & ")"
           myDestConn.Execute myDestSQL
   
           mySourceRecordset.MoveNext
       Next
   
       Main = DTSTaskExecResult_Success
   
   End If
   

Validating Data

The following ActiveX script, written in VBScript, modifies data on a row-by-row basis. Using the Customers table of the Northwind database as a source, the script moves the data into a new destination table in Northwind. The script validates several columns in the source data and transforms some column data before the row is inserted into the destination. The transformations change the Company Name to uppercase characters, trim leading and trailing spaces from the first name and last name, and fill the Region field with the string "unknown" if it is empty.

' Verify that there is a CompanyName. If there is, process the record. If
' there is not, skip the record.
If DTSSource("CompanyName") <> "" Then
    DTSDestination("CustomerID") = DTSSource("CustomerID")
    ' Uppercase the Company Name
    DTSDestination("CompanyName") = Ucase(DTSSource("CompanyName"))
    ' Trim leading and trailing spaces from the Name 
    DTSDestination("ContactName") = Trim(DTSSource("ContactName"))
    DTSDestination("ContactTitle") = DTSSource("ContactTitle")
    DTSDestination("Address") = DTSSource("Address")
    DTSDestination("City") = DTSSource("City")

    ' Check to see if the region is empty. If it is, fill it with string 
    ' of "unknown".
    If IsNull(DTSSource("Region").value  then
        DTSDestination("Region") = "unknown"
    Else
        DTSDestination("Region") = DTSSource("Region")
        End if

    DTSDestination("PostalCode") = DTSSource("PostalCode")
    DTSDestination("Country") = DTSSource("Country")
    DTSDestination("Phone") = DTSSource("Phone")
    DTSDestination("Fax") = DTSSource("Fax")

    ' This was a successful row. Send an OK status back for this row.
    Main = DTSTransformStat_OK
Else
    ' This row contained data that could not be processed.
    ' Skip it and get another row.
    Main = DTSTransformStat_SkipRow
End If

Using a Global Variable that Contains Columns of Data

This example uses an Execute SQL task to select data from a table, and populate global variables with the data from the first row returned. Each column is stored in its own global variable. The second half of this sample uses ActiveX script to display the data stored in the global variables.

To save row values into global variables