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 the SSIS script to an external file (.stx).
- link to the SSIS script from an external file (.stx). The link can be removed by pressing the button again.
You can check the following article on MSDN about good introductory information on SSIS Scripts or how you can boost your existing SSIS scripts.
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.
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.
Demonstration
Knowledge Base
- Where can I find the documentation for the Script Task Plus?
- Why can't I debug scripts? I set breakpoints and start the package in debugging mode, but it doesn't stop.
- How to provide a category and description for a parameter.
- How to sort parameters
- How to validate parameters
What's New
- Fixed: Failed to save parameters on OK (Thank you, Carl).
- Fixed: Failed to link to script, if there was already script in place when using SQL Server 2008.
- Fixed: Task generated warning "Script code is recompiled. Please manually load and re-save package." even when script code hasn't changed.
- New: Introduced task.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.