How to use an ActiveX script task to import data into a new Excel
file
Goal
1. Export data from the “pubs.dbo.authors” table to a new Excel
file.
2. The name of the Excel file should be derived from the current date and time.
3. The first row in the Excel file shows the column names.
Real World Example
A manager of a shop can run this DTS package on a daily basis to transfer Customer
order data from SQL Server into Excel files. The orders made on a particular
day will be transferred to the Excel file with the date as the file name so
that the shop manager can easily find out which Excel file includes what information.
Steps to Implement
1. In Enterprise Manager, open DTS designer.
2. Right-click on the white space of the DTS designer and choose Package Properties.
3. Go to the Global Variables tab and create a new global string variable called
fileName, which will hold the name of the Excel file to be created.
4. Click on the 
symbol
in the DTS designer’s connection list to set up the first connection
to the pubs database in your SQL Server.
5. Click on the 
symbol
in the DTS designer’s connection list to set up the second connection
to Excel. Under the File name text box, specify a file name, such as C:\test.xls.
6. highlight the sql server and the excel connection; click the 
symbol in the
DTS designer’s Tasks list to create a Transfer Data Task from the pubs
database to the Excel file.
7. From the Tasks list, click on the 
symbol to add the following ActiveX script
task:
Function Main()
Dim
appExcel
Dim newBook
Dim oSheet
dim oPackage
dim oConn
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
'Specify the column
name in the Excel worksheet
oSheet.Range("A1").Value
= "au_lname"
oSheet.Range("B1").Value
= "au_fname"
oSheet.Range("C1").Value
= "phone"
oSheet.Range("D1").Value
= "address"
oSheet.Range("E1").Value
= "city"
'Specify the name
of the new Excel file to be created
DTSGlobalVariables("fileName").Value
= "C:\" & Month(Now) & "-" &
Day(Now) & "-" & Year(Now) & "-" & Hour(Time)
& "-" &Minute(Time) & "-" &
Second(Time) & ".xls"
With newBook
.SaveAs
DTSGlobalVariables("fileName").Value
.save
End With
appExcel.quit
'dynamically specify
the destination Excel file
set oPackage = DTSGlobalVariables.parent
‘connection
2 is to the Excel file
set oConn = oPackage.connections(2)
oConn.datasource
= DTSGlobalVariables("fileName").Value
set oPackage = nothing
set oConn = nothing
Main
= DTSTaskExecResult_Success
End Function |
8. Highlight the ActiveX script task and the SQL server connection, then go
to the Workflow in the menu. Choose “On success” so that this ActiveX
script task will be executed before the Transfer Data Task. Here is how it should
look:
How to use an ActiveX script task to create a loop in DTS
Goal
1. We have a Transfer Data Task between two (2) SQL Servers.
2. We want this task to run 10 times through a loop.
Real World Example
A database administrator may need to create a testing environment with the
destination database being much bigger than the source database, but based on
the same type of data. A loop in DTS can be used to accomplish this.
Steps to Implement
1. In Enterprise Manager, open DTS designer.
2. Right-click on the white space of the DTS designer and choose Package Properties.
3. Go to the Global Variables tab and create a new global string variable called
count, which will be used to hold the number of times this task will be looped.
4. Click on the symbol in the DTS designer’s connection list to set up
the first connection to the source database in your SQL Server.
5. Click on the symbol in the DTS designer’s connection list to set up
the second connection to the destination database in your SQL server.
6. Highlight the two SQL Server connections and click on the
symbol in the DTS designer’s Tasks list so that it will create a Transfer
Data Task from the source database to the destination SQL Server database.
7. From the Tasks list, click on the symbol to add the following ActiveX script
task:
Function Main()
Dim pkg
Dim stpbegin
‘Increase
the count by 1 after each execution of the Transfer Data Task
DTSGlobalVariables("count").value
= DTSGlobalVariables("count").value + 1
‘decide if
we need to loop
if DTSGlobalVariables("count").value
< 11 then
set
pkg = DTSGlobalVariables.Parent
‘the
name of the task can be obtained by right click on the task, go to Workflow
Properties, then
‘choose
the options tab.
set
stpbegin = pkg.Steps("DTSStep_DTSDataPumpTask_1")
stpbegin.ExecutionStatus
= DTSStepExecStat_Waiting
end if
Main = DTSTaskExecResult_Success
End Function |
8. Highlight the ActiveX script task and the second SQL server connection; go
to the Workflow in the menu and choose “On success” so that this
ActiveX script task will be executed before the Transfer Data Task. Here is
how it should look:
© 2003 Microsoft