Understanding an ActiveX Script Task

The ActiveX Script task has been retained in SSIS 2005 for backward compatibility purposes. If you don't have any script code that you have to migrate from SQL Server 2000 DTS, you should use the Script task rather than the ActiveX Script task. The Script task provides you with a sophisticated way to implement such code as IntelliSense and integrated help, and it provides step-through debugging capability, which will be discussed in Chapter 7. The ActiveX Script task may be deprecated in a future release of SQL Server, so as a rule, it should not be used to implement new code.

The major component, called DTSGlobalVariables.Parent in SQL Server 2000 DTS, is no longer available in SSIS 2005. If you have an ActiveX task that manipulates package and task settings at run time, using the DTSGlobalVariables.Parent object, you cannot migrate it straight to the SSIS 2005 ActiveX Script task. SSIS 2005 suggests that you use Property Expressions and Package Configurations as the alternative for DTSGlobalVariables.Parent. These functionalities will be discussed in Chapter 8.

In SSIS 2005, you can still use ADO, FileSystemObject, and other COM objects. You can access variables through the DTSGlobalVariables object.

Implementing an ActiveX Script Task

In the following procedure, you will create a new package with a single ActiveX Script task, which will perform the same procedure that you implemented in the "Implement a Script Task" section. Retrieve the data from DimEmployee and export it to the custom format file. The ActiveX Script task also looks up the ExportFileName variable for the file name of the output file.

Create a New Package with a Single ActiveX Script Task

1. Start BIDS.

2. On the File menu, point to New and select Project.

3. Select Integration Services Project and type Chapter06_03 in the Name text box.

4. In the Location text box, specify C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005SbS\Chapter06_03-ActiveX. Make sure that the Create Directory For A Solution option is selected and click OK.

5. Right-click anywhere on the design surface of the Control Flow tab and select Variables.

6. Click the Add Variables button (in the top-left corner of the variables window).

7. Rename the new variable ExportFileName.

8. Change the new variable's Data Type from Int32 to String and type C:\Employees_AX.txt in the Value column.

9. In the Toolbox, in the Control Flow Items group, select ActiveX Script Task and drag it to the design surface of the Control Flow tab.

10. Double-click the new ActiveX Script task. The ActiveX Script Task Editor dialog box appears.

11. On the General page, change the Name property to ActiveX Script Task - Export Employees.

12. On the Script page, select the Script property in the property grid and click the ellipsis button displayed on the right side of the grid. The ActiveX Script dialog box appears.

13. Insert the following script in the ActiveX Script dialog box:

     Option Explicit Function Main()    Const RECORD_SEPARATOR = _         "----------------------------------------------------------"    Dim filename    Dim con, rs    Dim fso, writer    Dim col, row    Dim connectionString    Dim sqlSelectEmployee    connectionString = _          "Provider=SQLOLEDB;Data Source=localhost;" & _          "Initial Catalog=is2005sbsDW;Integrated Security=SSPI"    sqlSelectEmployee = _          "SELECT FirstName, LastName, MiddleName, " & _          "Gender, Title, DepartmentName, HireDate, LoginID, " & _          "EmailAddress, Phone FROM DimEmployee ORDER BY LastName"    fileName = DTSGlobalVariables("ExportFileName")    Set con = CreateObject("ADODB.Connection")    con.Open connectionString    Set rs = con.Execute(sqlSelectEmployee)    Set fso = CreateObject("Scripting.FileSystemObject")    Set writer = fso.CreateTextFile(fileName, True)    Do Until rs.EOF          For col = 0 To rs.Fields.Count - 1                writer.WriteLine rs.Fields(col).Name & ": " _                    & rs.Fields(col).Value          Next          writer.WriteLine RECORD_SEPARATOR          rs.MoveNext    Loop    writer.Close    rs.Close    con.Close End Function 

    This script is available in the text file saved in C:\Documents and Settings\<user-name>\My Documents\Microsoft Press\Chap06\Chapter06_03-ActiveX \Chapter06_03_AXScript.txt.

    The basic structure of the script is very similar to the one in the Script task exercise, although the script here doesn't have error-handling and logging code. The script retrieves data from DimEmployee in the is2005sbsDW database, using ADO, and writes out the text using FileSystemObject, with a result file name from the ExportFileName variable, using the DTSGlobalVariables object.

    Your screen looks like this:

    

14. Click OK.

15. In the ActiveX Script Task Editor dialog box, on the Script page, type Main in EntryMethod.

    The EntryMethod property is to point the function, which should be called first. If you don't have function in your script, this property doesn't need to be specified.

16. Click OK to close the ActiveX Script Task Editor dialog box.

17. Press F5 to execute the package. Confirm that the ActiveX Script Task - Export Employees turns green.

18. Open the root folder of the drive C in Windows Explorer and verify that Employees_AX.txt has been created. In Notepad, open C:\Employees_AX.txt and verify that the employee data has been exported correctly.

19. On the Debug toolbar, click Stop Debugging.

    Note 

    If the ActiveX Script task fails due to an [ActiveX Script Task] Error: Retrieving The File Name For A Component Failed With Error Code 0x0147763C error, open the script and replace localhost with the name of your computer.