Script Task

Overview

Script Task Plus is 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 ability to link to a common script code. Also the standard script task doesn't provide a way to expose user interface. We have implemented prebuilt user interface applied with attributes. The prebuilt user interface is so useful, it is in fact used to implement the user interface for the other components of the library. By resolving these shortcomings, we reduce the need for you to implement custom SSIS tasks.

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

  • Initialize page - select 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 SSIS script to external file (.stx).
  • Link ScriptLink Script - link to SSIS script from external file (.stx). The link can be removed by pressing the button again.

Requires SQL Server 2005 SP2.

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

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 property grid view and is available by selecting Initialize list item in CozyRoc Script Task dialog. We urge to familiarize yourself with PropertyGrid class because this is the class used for presentation and specified attributes below are related directly to it.

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

How to start?

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

    Add ReferenceAdd Reference

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

    Add ReferenceAdd Reference

  • Include Imports CozyRoc.SqlServer.SSIS.Attributes in script. It is also recommended to 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.

Simple Script Task Parameter

  • Open Design Script IDE and define a bool 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 code and close IDE. Select Initialize list item and you will see Bull parameter.

Predefined list parameter

  • Open Design Script IDE and define property attributed with List attribute. Use 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 dynamic list controlled by your script, you have to attribute your property with List attribute with 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

Text dialog parameter

  • Define property attributed with TextDialog. Attribute parameter title is the dialog title.
  • If you want to validate entered text before dialog is closed, provide 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 would like not to show ConnectionType property, specify System.ComponentModel.Browsable attribute to false.

Package Variable parameter

  • Define property attributed with 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 SQL query, you must attribute a property with SqlStatement attribute. The attribute's base constructor parameters are connection property and property containing SQL statement. Check sample code below for displaying 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 have to attribute a property with standard System.ComponentModel.Editor attribute. Check sample code below for implementation of 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 user to select Data Flow Destination component. If you want to ask user to select for example DataReader destination component instead, provide 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 category and description for 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 custom defined order, specify SortProperties attribute on the parameters class.
    <SortProperties(New String() {"Connection", "TargetFile", "Database"})> _
Public Class ScriptMain
...

How to filter parameters?

  • To filter parameters based on current state, specify FilterProperties attribute on the parameters class. Specify method name as attribute parameter. Specified method is dynamically called every time 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. Specified method will be called when 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.