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:
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.
In DTS, you can write the following types of scripts:
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.
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.
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.
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:
To add ActiveX Script Tasks:
To add ActiveX workflow scripts:
The range of functionality you can access from ActiveX scripts covers:
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.
The following DTS ActiveX script examples show:
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
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
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
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
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:
EmployeeID TerritoryID
1 03801
1 07960
3 40222
9 11747' 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
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
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
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