Tags: 
1.1, 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 Script - export the SSIS script to an external file (.stx).
  • Link 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 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 Reference
    • Include Imports CozyRoc.SqlServer.SSIS.Attributes in the script. It is also recommended that you include Imports System.Drawing.
    • Now you are ready to define your first Script Task Parameter.

    How to upgrade your script from 2005 to 2008

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

    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:

    • 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.

    Script Parameters

    • Open the Design Script IDE and define a boolean property like the following:
    • Save, build the code and close the IDE. Select the Initialize list item and you will see the Bull 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.
    • 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.

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

    • 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.
    • 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).
    • If you do not want to show the ConnectionType property, set the System.ComponentModel.Browsable attribute to false.
    • Define property attributed with the Variable attribute.
    • 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.
    • 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:
    • 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.
    • 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.
    • Category and description are provided with standard System.ComponentModel attributes Category and Description.
    • To sort parameters according to a custom defined order, specify the SortProperties attribute on the parameters class.
    • 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. You may also check the sample script Http Upload/Download Task.
    • 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. You may also check the sample script Send Mail Task.