Script tasks allow you to use the Microsoft Visual Studio environment to create and execute scripts in the VB. NET language. ActiveX tasks allow script execution from SQL Server 2000. Compared with ActiveX tasks, script tasks have some advantages. As shown below.
- A complete set of smart design Environments
- Easily pass parameters to the script
- Easily in the scriptCodeSet breakpoint
- You can pre-compile the script in binary format.
In the script task editing interface, 3-17 has a script language option to select the editing language. For SQL Server, you can only select VB. NET. If the precompilescriptintobinarycode option is set to true, the script will edit and speed up task running. The side effect of this operation is that the package will become larger.
Figure 3-17
Entrypoint is used to setProgramPortal. The readonlyvariables and readwritevariables options allow the SSIS variable to be passed in the script. readonlyvariables indicates that the variable is read-only and readwritevariables indicates that the variable can be read and written. Multiple variables can be separated by commas. Click design script to edit the script.
Click design script to open the Visual Studio environment. In this environment, you can debug and set breakpoints. The following column demonstrates how the script obtains the passed system variables. The script first checks whether the variable exists, and then displays the task name in a message box.
1 Imports System
2 Imports System. Data
3 Imports System. Math
4 Imports Microsoft. sqlserver. DTS. Runtime
5
6 Public Class Scriptmain
7 Public Sub Main ()
8 Dim Variables As Variables
9 If DTS. variables. Contains ( " Taskname " ) = True Then
10 DTS. variabledispenser. lockoneforread ( " Taskname " , Variables)
11 Dim Taskname As Object = Variables ( " Taskname " ). Value
12
13 End If
14
15 Msgbox ( " You are in the task: " & CSTR (Variables ( " Taskname " ). Value ))
16 DTS. taskresult = DTS. Results. Success
17 End sub
18
19 End Class
20
The package execution result is 3-18.
Figure 3-18
Here we hand over the ActiveX script. To ensure backward compatibility with SQL Server 2000 DTS, you can find that the ActiveX script task is not an advanced task, and the script task can replace it completely. Like a script task, it is also configured on the editing page. In earlier versions of SQL Server 2000, SQL Server supports multiple languages except VBScript. The entrymethod attribute specifies the method of this surname. You can also click the ellipsis to browse and select a script file.
Note: browsing selection overwrites the selected script.