Using ActiveX Scripts in DTS
New Information - SQL Server 2000 SP3.
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.
Note To use Microsoft ActiveX scripting in Microsoft SQL Server 2000 after uninstalling SQL Server 7.0, you must re-register the ActiveX scripting library (Axscphst.dll). Use the Regsvr32.exe registration utility and execute the following command (substituting your own drive letter) from the command prompt or the Windows Run command:
regsvr32.exe C:\Program Files\Microsoft SQL Server\80\Tools\Binnaxscphst.dll
If the registration is successful, a message box appears indicating that Axscphst.dll is registered properly.
Security Note Scripts can be the source of security vulnerabilities; they can invoke system functions without user knowledge or intervention and may contain security credentials in plain text. Review the script for security issues before use. For more information, see Security and Scripting.
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.
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.
- Use looping and conditional logic.
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
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='sapassword'" 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:
- Create a table named NewEmployeeTerritory in the Northwind database that has a schema identical to the EmployeeTerritory table.
- Insert the following four records into the NewEmployeeTerritory table:
1 03801 1 07960 3 40222 9 11747
- Create a new DTS package in DTS Designer.
- Drag an ActiveX Script task onto the design sheet.
- 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='sapassword'" 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';password = 'sapassword'" 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
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
To retrieve row value data
Using a Global Variable that Contains a Rowset
This sample uses an Execute SQL task to retrieve multiple rows from a table, and stores this rowset into a global variable. The second part of the example uses ActiveX scripting to display the rows stored in the global variable.
To save an entire rowset into a global variable
To retrieve the rowset data stored in a global variable