Script Component


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

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

Looking for older releases? Check here.

Overview

Note: Please install at least SQL Server 2005 SP2 before using this component.

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 ScriptExport Script - export the SSIS script to an external file (.scx).
  • Link ScriptLink Script - link to the SSIS script from an external file (.scx). The link can be removed by pressing the button again.

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

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

Component input parameter

  • Define the property associated with the Input attribute.
    <Input()> _
    Property Input() As String
        Get
            Input = m_input
        End Get
        Set(ByVal value As String)
            m_input = value
        End Set
    End Property

Component input column parameter

  • 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.
    <InputColumn("Input")> _
    Property InputColumn() As String
        Get
            InputColumn = m_inputColumn
        End Get
        Set(ByVal value As String)
            m_inputColumn = value
        End Set
    End Property

Component input external column parameter

  • 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.
    <InputExternalColumn("Input")> _
    Property InputExternalColumn() As String
        Get
            InputExternalColumn = m_inputExternalColumn
        End Get
        Set(ByVal value As String)
            m_inputExternalColumn = value
        End Set
    End Property

Component input virtual column parameter

  • 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.
    <InputVirtualColumn("Input")> _
    Property InputVirtualColumn() As String
        Get
            InputVirtualColumn = m_inputVirtualColumn
        End Get
        Set(ByVal value As String)
            m_inputVirtualColumn = value
        End Set
    End Property

Component output parameter

  • Define the property associated with the Output attribute.
    <Output()> _
    Property Output() As String
        Get
            Output = m_output
        End Get
        Set(ByVal value As String)
            m_output = value
        End Set
    End Property

Component output column parameter

  • 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.
    <OutputColumn("Output")> _
    Property OutputColumn() As String
        Get
            OutputColumn = m_outputColumn
        End Get
        Set(ByVal value As String)
            m_outputColumn = value
        End Set
    End Property

Component output external column parameter

  • 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.
    <OutputExternalColumn("Output")> _
    Property OutputExternalColumn() As String
        Get
            OutputExternalColumn = m_outputExternalColumn
        End Get
        Set(ByVal value As String)
            m_outputExternalColumn = value
        End Set
    End Property

Component Column Mappings tab (1.4 SR-3)

  • To enable the script Mappings tab, associate your script main class with the ColumnMappings attribute.
    <ColumnMappings()> _
    Public Class ScriptMain
        Inherits UserComponent
    ...