Overview

The Script Component Plus is an SSIS Data Flow component and it is an extension of the standard Microsoft Script Component. It provides benefits similar to Script Task Plus, but for the Microsoft Data Flow Task. You can reuse your data flow script code and build a user interface to simplify your script configuration. These features reduce the need to implement custom SSIS script components. See the public script repository for useful components and samples.

The component dialog contains the following elements:

  • Setup tab - Select script initialization parameters. The Setup tab configuration is made with attributes in the script.
  • Advanced tab - Setup of the standard Microsoft Script Component configuration parameters. You can modify the script code in this tab.
  • Export Script - export the SSIS script to an external file (.scx).
  • Link Script - link to the SSIS script from an external file (.scx). The link can be removed by pressing the button again.
Please install at least SQL Server 2005 SP2 before using this component.

Demonstration


    Setup

    The initialization and user interface for a script is defined by implementing properties on the ScriptMain class. The user interface is contained in the property grid view and it is available by selecting the Setup tab in the COZYROC Script Component dialog. We urge you to familiarize yourself with the PropertyGrid class. This is the class used for presentation; specified attributes below are related directly to it.

    See the samples below for how to implement script component parameters. These samples are specific to the script component. The attributes demonstrated in the Script Task Plus samples can also be used.


    How to upgrade a 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 an SSIS 2005 package and insert the Data Flow Task. Go to data flow design and insert the COZYROC Script Component Plus.
    • 2. Load a script in the component 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 Component 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.

    Component connection parameter

    The Connection attribute described in Script Task Plus has an additional constructor with the isConnectionID parameter. When the parameter is set to true, the component connection property will contain the connection ID, instead of the connection name. Set this parameter to true in your script component to be able to use the package connection manager.


    Extended script component base class

    The extended script component base class contains the following additional features:

    • HostComponent - property to access the hosting component object.
    • DesignConnections - property containing package connections list. To be used only in design mode.

    Extended buffer base class

    The extended buffer base class contains the following additional features:

    • Buffer - property to access the internal buffer object. This object is usually needed when working with dynamically processed columns.
    • StaticInputColumns - access to the list of specified static input columns.
    • StaticOutputColumns - access to the list of specified static output columns.

    Script Attributes

    Define the property associated with the Input attribute.

    Define property attributed with the InputColumn attribute, setting for parameter the property containing input name. Defining InputColumn without parameter will retrieve the first component input columns. You can ask the user for single or multiple columns selection.

    Define property attributed with the InputExternalColumn attribute, setting for parameter the property containing input name. Defining InputExternalColumn without parameter will retrieve the first component input external columns. You can ask the user for single or multiple columns selection.

    Define property attributed with the InputVirtualColumn attribute, setting for parameter the property containing input name. Defining InputVirtualColumn without parameter will retrieve the first component input virtual columns. You can ask the user for single or multiple columns selection.

    Define the property associated with the Output attribute.

    Define property attributed with the OutputColumn attribute, setting for parameter the property containing output name. Defining OutputColumn without parameter will retrieve the first component output columns. You can ask the user for single or multiple columns selection.

    Define property attributed with the OutputExternalColumn attribute, setting for parameter the property containing output name. Defining OutputExternalColumn without parameter will retrieve the first component output external columns. You can ask the user for single or multiple columns selection.

    To enable the script Mappings tab, associate your script main class with the ColumnMappings attribute.