Script Component

Overview

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

Script Component Plus is SSIS Data Flow component and it is extension of the standard Microsoft Script Component. It does provide similar benefits like Script Task Plus, but for Microsoft Data Flow Task. You can reuse your data flow script code and build user interface to simplify your script configuration. These features reduce the need to implement custom SSIS script components. Check 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 SSIS script to external file (.scx).
  • Link ScriptLink Script - link to SSIS script from 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 property grid view and it is available by selecting Setup tab in CozyRoc Script Component dialog. We urge you to familiarize yourself with the PropertyGrid class. This is the class used for presentation and specified attributes below are related directly to it.

Check the samples below 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 script from 2005 to 2008?

Most of the scripts published in the public script repository are for SQL 2005. However they could be easily upgraded to SQL 2008 by following these 10 steps:

Note: A precondition for this process to work you have to have both SQL 2005 and 2008 installed side-by-side on the same machine.

  • 1. Create SSIS 2005 package and insert Data Flow Task. Go to data flow design and insert CozyRoc Script Component Plus.
  • 2. Load a script in the component and break the link to the script. Save the package.
  • 3. Go to 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 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 script IDE.
  • 7. Right-click on the script project and select Properties.
  • 8. Go to References tab and add reference to CozyRoc.SSISPlus.2008 from "Microsoft SQL Server\100\SDK\Assemblies" folder.
  • 9. Save and close 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 additional constructor with isConnectionID parameter. When the parameter is set to true, the component connection property will contain connection ID, instead of connection name. Set this parameter to true in your script component, to be able to use 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 bufer 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 property attributed with 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 InputColumn attribute, setting for parameter the property containing input name. Defining InputColumn without parameter will retrieve the first component input columns. You can ask 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 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 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 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 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 property attributed with 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 OutputColumn attribute, setting for parameter the property containing output name. Defining OutputColumn without parameter will retrieve the first component output columns. You can ask 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 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 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