Script Task

Overview

Script Task Plus is an SSIS control flow task and it is an extension of the standard Microsoft Script Task. The standard script task doesn't have the ability to reuse script code. If the SSIS developer wants to reuse the same script in a different task, he has to copy and paste the code. Our solution is to extend the standard script task with the ability to link to common script code. Also the standard script task doesn't provide a way to expose the user interface. We have implemented a prebuilt user interface which is applied with attributes. The user interface for the other components in the COZYROC library is implemented by using this prebuilt user interface, thereby illustrating its adaptability. By resolving the shortcomings of the standard Microsoft Script Task, we reduce the need for you to implement custom SSIS tasks. Check the public script repository for useful components and samples.

The task dialog is very similar to the standard Microsoft Script Task dialog and has the following additional features:

  • Initialize page - select the script initialization parameters. These parameters will be initialized before the Main script subroutine is executed. The page configuration is made with attributes in the script.
  • Export ScriptExport Script - export the SSIS script to an external file (.stx).
  • Link ScriptLink Script - link to the SSIS script from an external file (.stx). The link can be removed by pressing the button again.

Requires SQL Server 2005 SP2 or a later version.

You can check the following article on MSDN about good introductory information on SSIS Scripts or how you can boost your existing SSIS scripts.

Demonstration

Setup

The initialization and user interface for a script is defined by implementing properties on the script EntryPoint class. The user interface is contained in the property grid view and is available by selecting the Initialize list item in the COZYROC Script Task dialog. We urge you to familiarize yourself with PropertyGrid class because this is the class used for presentation and the specified attributes below are related directly to it.

Check the samples below for information on how to implement your first script task parameters.

How to start

  • Go to Toolbox and open the Choose Toolbox Items ... dialog. Press the SSIS Control Flow Items tab and select Script Task Plus.

    Add ReferenceAdd Reference

  • Drag Script Task Plus into your control flow package and open the Design Script IDE.
  • Add a reference in the script project to CozyRoc.SSISPlus

    Add ReferenceAdd Reference

  • Include Imports CozyRoc.SqlServer.SSIS.Attributes in the script. It is also recommended that you include Imports System.Drawing.
    Imports System.Drawing
Imports CozyRoc.SqlServer.SSIS.Attributes

Public Class ScriptMain
  • Now you are ready to define your first Script Task Parameter.

How to upgrade your script from 2005 to 2008

Most of the scripts published in the public script repository are for SQL 2005. However they can be easily upgraded to SQL 2008 by following these 10 steps:

Note: In order for this process to work, you must have both SQL 2005 and 2008 installed side-by-side on the same machine.

  • 1. Create the SSIS 2005 package and insert COZYROC Script Task Plus.
  • 2. Load a script in the task and break the link to the script. Save the package.
  • 3. Go to the SSIS 2008 solution. Right-click on the solution and select 'Add Existing Package'.
  • 4. Select the package you have created in step 1. SSIS 2008 will do an automatic upgrade of the package and show some warnings.
  • 5. Open the imported package.
  • 6. Double-click on the COZYROC Script Task Plus and enter the script IDE.
  • 7. Right-click on the script project and select Properties.
  • 8. Go to the References tab and add a reference to CozyRoc.SSISPlus.2008 from the "Microsoft SQL Server\100\SDK\Assemblies" folder.
  • 9. Save and close the project settings. Make sure the script compiles.
  • 10. Close the script IDE and export the script.

Simple Script Task Parameter

  • Open the Design Script IDE and define a boolean property like the following:
    Property Bull() As Boolean
    Get
        Bull = m_bull
    End Get
    Set(ByVal value As Boolean)
        m_bull = value
    End Set
End Property

Private m_bull As Boolean
  • Save, build the code and close the IDE. Select the Initialize list item and you will see the Bull parameter.

Predefined list parameter

  • Open the Design Script IDE and define the property attributed with the List attribute. Use the attribute constructor with array parameter. This is your predefined list.
    <List(New Object() {"USA", "Japan", "Panama"})> _
Property Country() As String
    Get
        Country = m_country
    End Get
    Set(ByVal value As String)
        m_country = value
    End Set
End Property

Private m_country As String

Flexible list parameter

  • To define a dynamic list controlled by your script, you have to attribute your property with the List attribute with a constructor where you specify the class method returning the list.
    <List("GetAnimal")> _
Property Animal() As String
    Get
        Animal = m_animal
    End Get
    Set(ByVal value As String)
        m_animal = value
    End Set
End Property

Public Function GetAnimal() As Object()
    If Me.Mammal
        GetAnimal = ...
    Else
        GetAnimal = ...
End If
End Function

Private m_animal As String

Multiple selection from a list (1.4)

Starting from SSIS+ 1.4, you can ask the user to make multiple selections from a list. The multiple selections are separated with ",vbLf".

Text dialog parameter

  • Define property attributed with TextDialog. Attribute parameter title is the dialog title.
  • If you want to validate entered text before the dialog is closed, provide a second attribute parameter validateMethod.
    <TextDialog("Enter a string", "ValidateString")> _
<Description("This is a dialog string parameter.")> _
Property DialogString() As String
    Get
        DialogString = m_string
    End Get
    Set(ByVal value As String)
        m_string = value
    End Set
End Property

Public Function ValidateString(ByVal value As String, ByRef errMessage As String) As Boolean
    Dim result As Boolean = True

    If value <> "Hello World" Then
        result = False
        errMessage = "Expected: Hello World"
    End If

    ValidateString = result
End Function

Package connection parameter

  • Define property returning connection type. Connection types can be: OLEDB, ODBC, ADO, ADO.NET, FILE, FLATFILE.
  • Define property attributed with Connection attribute, setting for parameter the property containing connection type(s).
    ReadOnly Property ConnectionType() As String()
    Get
        ConnectionType = New String() {"ADO.NET"}
    End Get
End Property

<Connection("ConnectionType")> _
Property Connection() As String
    Get
        Connection = m_connection
    End Get
    Set(ByVal value As String)
        m_connection = value
    End Set
End Property

Private m_connection As String
  • If you do not want to show the ConnectionType property, set the System.ComponentModel.Browsable attribute to false.

Package Variable parameter

  • Define property attributed with the Variable attribute.
    <Variable()> _
Property Variable() As String
    Get
        Variable = m_variable
    End Get
    Set(ByVal value As String)
        m_variable = value
    End Set
End Property

SQL query list parameter

  • To implement a parameter whose list is a result of a SQL query, you must attribute a property with the SqlStatement attribute. The attribute's base constructor parameters are connection property and property containing SQL statement. Check the sample code below for how to display a list of databases and a list of tables.
    <SqlStatement("Connection", "SqlDatabases")> _
Property Database() As String
    Get
        Database = m_database
    End Get
    Set(ByVal value As String)
        m_database = value
    End Set
End Property

ReadOnly Property SqlDatabases() As String
    Get
        SqlDatabases = "SELECT name FROM sys.databases"
    End Get
End Property

<SqlStatement("Connection", "Database", "SqlTables", Nothing, "GetTables")> _
Property Table() As String
    Get
        Table = m_table
    End Get
    Set(ByVal value As String)
        m_table = value
    End Set
End Property

ReadOnly Property SqlTables() As String
    Get
        SqlTables = "SELECT S.name, T.name FROM sys.tables AS T INNER JOIN sys.schemas AS S on T.schema_id = S.schema_id"
    End Get
End Property

Public Function GetTables(ByVal rows As DataRowCollection) As ArrayList
    Dim result As ArrayList = New ArrayList()

    For Each row As DataRow In rows
        result.Add(String.Format("[{0}].[{1}]", row(0), row(1)))
    Next

    GetTables = result
End Function

Custom editor parameter

  • To implement your own custom editor, you must attribute a property with standard System.ComponentModel.Editor attribute. Check the sample code below for implementation of the file open dialog editor:
    '
' Implements File Open Dialog Editor.
' 
Class FileEditor
    Inherits UITypeEditor

    ' Methods
    Public Sub New()
    End Sub


    Public Overrides Function GetEditStyle(ByVal context As ITypeDescriptorContext) As UITypeEditorEditStyle
        Return UITypeEditorEditStyle.Modal
    End Function    ' GetEditStyle


    Public Overrides Function EditValue( _
        ByVal context As ITypeDescriptorContext, _
        ByVal provider As IServiceProvider, _
        ByVal value As Object) As Object

        If context.PropertyDescriptor.Name <> "TargetFile" Then
            Return value
        End If

        Using dialog As OpenFileDialog = New OpenFileDialog()
            If dialog.ShowDialog() = DialogResult.OK Then
                value = dialog.FileName
            End If
        End Using

        Return value
    End Function
End Class   ' FileEditor

...

<Editor(GetType(FileEditor), GetType(UITypeEditor))> _
Property TargetFile() As String
    Get
        TargetFile = m_targetFile
    End Get
    Set(ByVal value As String)
        m_targetFile = value
    End Set
End Property

Private m_targetFile As String

Data Flow Destination dialog parameter (1.3)

  • Define property attributed with DataFlowDestination. Attribute parameter connectionProperty is the property containing the package connection manager.
  • By default, the dialog asks the user to select the Data Flow Destination component. If you want to ask the user to select, for example, the DataReader destination component instead, provide a second attribute parameter destinationType, set to DataReader.
    <DataFlowDestination("PackageConnection", "DataReader")> _
<Description("Select DataReader Destination component.")> _
    Public Property DataFlowDestination() As String
        Get
            DataFlowDestination = m_destination
        End Get
        Set(ByVal value As String)
            m_destination = value
        End Set
    End Property    ' DataFlowDestination

FTP browser dialog parameter (1.3)

  • Define property attributed with FtpRemoteBrowserDialog. The first attribute parameter connectionProperty is the property containing the secure FTP (SFTP, FTPS) connection manager. The second attribute parameter isFileMode specifies if you want the browser to be in directory or file selection mode.
    <FtpRemoteBrowserDialog("FtpConnection", false)> _
<Description("Select remote FTP folder.")> _
    Public Property RemoteFolder() As String
        Get
            RemoteFolder = m_remote
        End Get
        Set(ByVal value As String)
            m_remote = value
        End Set
    End Property    ' RemoteFolder

How to provide a category and description for a parameter

  • Category and description are provided with standard System.ComponentModel attributes Category and Description.
    <Connection("ConnectionType")> _
<Category("General")> _
<Description("Select connection you would like to use.")> _
Property Connection() As String
    Get
        Connection = m_connection
    End Get
    Set(ByVal value As String)
        m_connection = value
    End Set
End Property

How to sort parameters

  • To sort parameters according to a custom defined order, specify the SortProperties attribute on the parameters class.
    <SortProperties(New String() {"Connection", "TargetFile", "Database"})> _
Public Class ScriptMain
...

How to filter parameters

  • To filter parameters based on the current state, specify the FilterProperties attribute on the parameters class. Specify the method name as attribute parameter. The specified method is dynamically called every time the class parameters are refreshed.
    <FilterProperties("GetProperties"})> _
Public Class ScriptMain

...

Public Function GetProperties() As String()
    Dim result As New ArrayList

    Call result.Add("Action")  ' Action is always included.

    If Me.Action = "Upload File" Then
        Call result.Add("SourceFile")
    ElseIf Me.Action = "Download File" Then
        Call result.Add("TargetFolder")
    End If

    Return CType(result.ToArray(GetType(String)), String())
End Function

    You may also check the sample script Http Upload/Download Task.

How to validate parameters

  • To validate parameters, specify ValidateProperties attribute on the parameters class. Specify method name as attribute parameter. The specified method will be called when the user presses OK in the dialog.
    <ValidateProperties("Validate"})> _
Public Class ScriptMain

...

Public Function Validate(ByRef errMessage As String) As Boolean
    Dim result As Boolean

    result = False

    If Me.FromSender = "" Then
        errMessage = "Please specify FromSender."
    ElseIf Me.ToRecipient = "" Then
        errMessage = "Please specify ToRecipient."
    ElseIf Me.Subject = "" Then
        errMessage = "Please specify Subject."
    ElseIf Me.Message = "" Then
        errMessage = "Please specify Message."
    Else
        result = True
    End If

    Validate = result
End Function

    You may also check the sample script Send Mail Task.