Common SSIS packages-scripts and component tasks

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.