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:

FunctionMain()

DimappExcel
DimnewBook
DimoSheet

dimoPackage
dimoConn

SetappExcel=CreateObject("Excel.Application")
SetnewBook=appExcel.Workbooks.Add
SetoSheet=newBook.Worksheets(1)

'SpecifythecolumnnameintheExcelworksheet

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"


'SpecifythenameofthenewExcelfiletobecreated

DTSGlobalVariables("fileName").Value="C:"&Month(Now)&"-"&
Day(Now)&"-"&Year(Now)&"-"&Hour(Time)&"-"&Minute(Time)&"-"&
Second(Time)&".xls"

WithnewBook
.SaveAsDTSGlobalVariables("fileName").Value
.save
EndWith

appExcel.quit

'dynamicallyspecifythedestinationExcelfile

setoPackage=DTSGlobalVariables.parent

‘connection2istotheExcelfile
setoConn=oPackage.connections(2)
oConn.datasource=DTSGlobalVariables("fileName").Value

setoPackage=nothing
setoConn=nothing

Main=DTSTaskExecResult_Success

EndFunction

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: