Microsoft® ActiveX® Script tasks can be used to set connection and other object properties dynamically, perform file operations (such as logging to a file), create ADO or COM objects, and load data from a file into a global variable. These are examples of ActiveX Script tasks.
This ActiveX script changes the server name of a connection dynamically. You can use similar code for packages where you want to execute an operation against a list of servers:
function main()
dim oPackage
dim oConn
'get package's object handle
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections(1)
oConn.datasource = "newServerName"
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
end function
The following two ActiveX script tasks, written in Microsoft Visual Basic® Scripting Edition, demonstrate how to create a file, write a line to a file, open a file, and read a line in a file to a global variable:
'Create and write line to a file.
function main()
'Initialize file system object and file.
Dim fso
Dim a
'Create the text file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.CreateTextFile("c:\test.txt")
'Write a line to the text file.
a.WriteLine("This is a test")
a.Close
Main = DTSTaskExecResult_Success
end function
'Open the file.
function main()
Dim fso
Dim a
'Open the text file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.OpenTextFile("c:\test.txt")
'Read line from the file to the global variable.
DTSGlobalVariables("myGlobalVar").Value = a.ReadLine
a.Close
Main = DTSTaskExecResult_Success
end function
The following ActiveX script task, written in Visual Basic Scripting Edition, demonstrates how to create some typical ADO objects, open a recordset, and perform a simple routine that validates the number of rows:
Dim cn
Dim rs
Dim fld
Dim cmdText
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
function main()
'Set ADO connection properties.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "."
cn.Properties("Initial Catalog").Value = "Northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
cmdText = "Select 'rowcount' = Count(*) from Customers"
rs.Open cmdText, cn
Set Flds = rs.Fields
Set fld = Flds("rowcount")
If fld.Value = 91 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
end function