Script Task

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Join VIP ----- Questions?

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.