How to use an ActiveX script task to import data into a new Excel file

Goal

1. 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. 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. 

1. 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: