How to use an ActiveX script task to import data into a new Excel file-优快云博客

本文介绍一种使用DTS将SQL Server中的数据定期自动导出到Excel文件的方法。通过设置DTS包任务，可以每天从指定的SQL表中提取数据，并将其保存到一个根据当前日期和时间动态命名的Excel文件中，方便管理者轻松追踪特定日期的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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: