Task

Overview

Amazon S3 Task is SSIS Control Flow task for sending and receiving files to Amazon S3 service.

Setup

Use the General page of the Amazon S3 Task Editor dialog to configure the options needed to send and receive files.

Options

Name

Specify task name.

Description

Specify task description.

Action

Specify task action. This property has the options listed in the following table.

Value	Description
Send file	Sends file. Selecting this value displays the dynamic options IsLocalVariable, Local, LocalVariable, ContentType, AccessPolicy, AmazonS3Connection, Bucket, IsRemoteVariable, Remote, RemoteVariable.
Receive file	Receives file. Selecting this value displays the dynamic options IsLocalVariable, Local, LocalVariable, AmazonS3Connection, Bucket, IsRemoteVariable, Remote, RemoteVariable.
Remove remote file	Removes remote file. Selecting this value displays the dynamic options AmazonS3Connection, Bucket, IsRemoteVariable, Remote, RemoteVariable.
Get remote files list	Gets list of remote files. Selecting this value displays the dynamic options ResultVariable, AmazonS3Connection, Bucket.

IsLocalVariable

Indicates whether the local path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The local path is stored in a variable. Selecting the value displays the dynamic option LocalVariable.
False	The local path is specified in a File connection manager. Selecting the value displays the dynamic option Local.

Local

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager Editor

LocalVariable

Contains local path or Stream object. Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ContentType

Specify content type of uploaded file.

AccessPolicy

Select access policy of uploaded file.

AmazonS3Connection

Select an existing Amazon S3 connection manager, or click <New connection...> to create a connection manager.

Related Topics: Amazon S3 Connection Manager

Bucket

Select bucket from your Amazon S3 account.

IsRemoteVariable

Indicates whether the remote name is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The remote name is stored in a variable. Selecting the value displays the dynamic option RemoteVariable.
False	The remote name is specified in a File connection manager. Selecting the value displays the dynamic option Remote.

Remote

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

RemoteVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Overview

PGP Task is SSIS Control Flow task for encryption and decryption using the OpenPGP (RFC 2440) standard.

Setup

Use the General page of the PGP Task Editor dialog to configure the options needed to encrypt, decrypt, create or verify file signature.

Options

Name

Specify task name.

Description

Specify task description.

Action

Specify task action. This property has the options listed in the following table.

Value	Description
Encrypt file	Encrypts file. Selecting this value displays the dynamic options IsPublicKeyRingVariable, PublicKeyRing, PublicKeyRingVariable, Key, OutputASCII, IsSourceVariable, Source, SourceVariable, RemoveSource, IsTargetVariable, Target, TargetVariable, OverwriteExisting.
Decrypt file	Encrypts file. Selecting this value displays the dynamic options IsSecretKeyRingVariable, SecretKeyRing, SecretKeyRingVariable, Password, IsSourceVariable, Source, SourceVariable, IsTargetVariable, Target, TargetVariable, OverwriteExisting.
Create file signature	Creates file signature. Selecting this value displays the dynamic options IsSecretKeyRingVariable, SecretKeyRing, SecretKeyRingVariable, Key, Password, OutputASCII, IsSourceVariable, Source, SourceVariable, IsSignatureVariable, Signature, SignatureVariable, OverwriteExisting.
Verify file signature	Verifies file signature. Selecting this value displays the dynamic options IsPublicKeyRingVariable, PublicKeyRing, PublicKeyRingVariable, ResultVariable, IsSourceVariable, Source, SourceVariable, IsSignatureVariable, Signature, SignatureVariable.

IsPublicKeyRingVariable

Indicates whether the public key ring path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The public key ring path is stored in a variable. Selecting the value displays the dynamic option PublicKeyRingVariable.
False	The public key ring path is specified in a File connection manager. Selecting the value displays the dynamic option PublicKeyRing.

PublicKeyRing

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager Editor

PublicKeyRingVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Key

Select key(s) from key ring.

OutputASCII

Select to output 7-bit ASCII data instead of binary data.

IsSourceVariable

Indicates whether the source path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The source path is stored in a variable. Selecting the value displays the dynamic option SourceVariable.
False	The source path is specified in a File connection manager. Selecting the value displays the dynamic option Source.

Source

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager Editor

SourceVariable

Contains source path or input Stream object. Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

RemoveSource

Specify source file to be removed after encryption.

IsTargetVariable

Indicates whether the target path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The target path is stored in a variable. Selecting the value displays the dynamic option TargetVariable.
False	The target path is specified in a File connection manager. Selecting the value displays the dynamic option Target.

Target

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

TargetVariable

Contains target path or output Stream object. Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

OverwriteExisting

Specify to overwrite existing file.

IsSecretKeyRingVariable

Indicates whether the secret key ring path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The secret key ring path is stored in a variable. Selecting the value displays the dynamic option SecretKeyRingVariable.
False	The secret key ring path is specified in a File connection manager. Selecting the value displays the dynamic option SecretKeyRing.

SecretKeyRing

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

SecretKeyRingVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

IsSignatureVariable

Indicates whether the signature path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The signature path is stored in a variable. Selecting the value displays the dynamic option SignatureVariable.
False	The signature path is specified in a File connection manager. Selecting the value displays the dynamic option Signature.

Signature

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

SignatureVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The variable has to be type Boolean and it will contain the verificiation result.

Related Topics: Integration Services Variables, Add Variable

Overview

Zip Task is SSIS Control Flow task for compression and decompression of Zip, GZip, BZip2, Unix and Tar archives.

Setup

Use the General page of the Zip Task Editor dialog to configure the options needed for archive compression and decompression.

Options

Name

Specify task name.

Description

Specify task description.

Action

Specify task action. This property has the options listed in the following table.

Value	Description
Compress	Compress files into archive. IsSourceVariable, Source, SourceVariable specify input files. IsTargetVariable, Target, TargetVariable specify output archive destination.
Decompress	Decompress files from archive. IsSourceVariable, Source, SourceVariable specify input archive file. IsTargetVariable, Target, TargetVariable specify output files destination.

CompressionType

Specify type of compression or decompression. This property has the options listed in the following table.

Value	Description
Zip	Type of archive is Zip.
Tar	Type of archive is Tar.

CompressionLevel

Visible only when specified action is Compress.
When Zip compression type is selected, the parameter specifies the level of compression.
When Tar compression type is selected, the parameter specifies type of compressor used - GZip, BZip2, Unix, None.

Password

Visible only when specified compression type is Zip. Specify archive encryption password.

IsSourceVariable

Indicates whether the source is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The source is stored in a variable. Selecting the value displays the dynamic option SourceVariable.
False	The source is specified in a File connection manager. Selecting the value displays the dynamic option Source.

Source

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

SourceVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

RemoveSource

Visible only when specified action is Compress. Specify if task has to remove source files after compression is done.

IncludeSubfolders

Visible only when specified action is Compress. Specify if task has to recursively include all sub-files and sub-folders.

IsTargetVariable

Indicates whether the target is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The target is stored in a variable. Selecting the value displays the dynamic option TargetVariable.
False	The target is specified in a File connection manager. Selecting the value displays the dynamic option Target.

Target

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

TargetVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

OverwriteExisting

Visible only when specified action is Decompress. Specify if task has to overwrite existing target files.

Overview

SSH Execute Task is SSIS Control Flow task for secure execution of shell commands on a remote SSH server.

Setup

Use the General page of the SSH Execute Task Editor dialog to configure the options needed to run a command on remote SSH server.

Options

Name

Specify task name.

Description

Specify task description.

ExecConnection

Select an existing SSH connection manager, or click <New connection...> to create a connection manager.

Related Topics: SSH Connection Manager

StopOnFailure

Specify whether the SSH Execute Task terminates if command fails.

CommandType

Select the source type of the command that the task runs. This property has the options listed in the following table.

Value	Description
Direct input	Set the command in the task. Selecting this value displays the dynamic option CommandDirect.
File connection	Select a file that contains the command. Selecting this value displays the dynamic option CommandFile.
Variable	Set to a variable that defines the command. Selecting this value displays the dynamic option CommandVariable.

CommandDirect

Type the command to execute in the option box or click the browse button (...) to type the command in the Enter the command dialog.

CommandFile

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

CommandVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

OutputVariable

Select an existing user-defined variable, or click <New variable...> to create a variable where command output is stored.

Related Topics: Integration Services Variables, Add Variable

ErrorVariable

Select an existing user-defined variable, or click <New variable...> to create a variable where command error message is stored.

Related Topics: Integration Services Variables, Add Variable

Overview

SFTP Task is SSIS Control Flow task for Secure-FTP (SFTP) communications.

Note:

1. If you have issues running the SFTP task under SQL Server Agent Job, please check SSH Connection Manager FAQ.

2. SFTP protocol is different than FTPS protocol. You may check the sample FTPS script and see if it works for your needs.

Setup

Use the General page of the SFTP Task Editor dialog to configure the options needed to send or receive files from an SFTP server and to manage directories and files.

Options

Name

Specify task name.

Description

Specify task description.

StopOnFailure

Specify whether the SSH Execute Task terminates if command fails.

Action

Specify task action. This property has the options listed in the following table.

Value	Description
Send files	Send files. Selecting this value displays the dynamic options IsLocalVariable, Local, LocalVariable, FtpConnection, IsRemoteVariable, Remote, RemoteVariable, OverwriteRemote.
Receive files	Receive files. Selecting this value display the dynamic options IsLocalVariable, Local, LocalVariable, OverwriteLocal FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Create remote directory	Create a remote directory. Selecting this value display the dynamic options FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Remove remote directory	Remove a remote directory. Selecting this value display the dynamic options FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Delete remote files	Delete remote files. Selecting this value display the dynamic options FtpConnection, IsRemoteVariable, Remote, RemoteVariable.
Rename remote file	Rename a remote file. Selecting this value display the dynamic options FtpConnection, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable.
Get remote files list (1.2 SR-1)	Get list of remote files. Selecting this value display the dynamic options ResultVariable, FtpConnection, IsRemoteVariable, Remote, RemoteVariable.

IsLocalVariable

Indicates whether the local path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The local path is stored in a variable. Selecting the value displays the dynamic option LocalVariable.
False	The local path is specified in a File connection manager. Selecting the value displays the dynamic option Local.

Local

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager Editor

LocalVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

FtpConnection

Select an existing SSH connection manager, or click <New connection...> to create a connection manager.

Related Topics: SSH Connection Manager

IsRemoteVariable

Indicates whether the remote path is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The remote path is stored in a variable. Selecting the value displays the dynamic option RemoteVariable.
False	The remote path is explicitly specified on the remote server. Selecting the value displays the dynamic option Remote.

Remote

Select remote path on the remote server.

RemoteVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

OverwriteRemote

Specify whether remote file can be overwritten.

Remark(s):
If you are sending 0-length files, make sure this option is set to True. Otherwise the remote file would not be created.

OverwriteLocal

Specify whether local file can be overwritten.

IsOldNameVariable

Indicates whether the old name is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The old name is stored in a variable. Selecting the value displays the dynamic option OldNameVariable.
False	The old name is specified in a File connection manager. Selecting the value displays the dynamic option OldName.

OldName

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

OldNameVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

IsNewNameVariable

Indicates whether the new name is stored in a variable. This property has the options listed in the following table.

Value	Description
True	The new name is stored in a variable. Selecting the value displays the dynamic option NewNameVariable.
False	The new name is specified in a File connection manager. Selecting the value displays the dynamic option NewName.

NewName

Select an existing File connection manager, or click <New connection...> to create a connection manager.

Related topics: File Connection Manager

NewNameVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable (1.2 SR-1)

Select an existing user-defined variable, or click <New variable...> to create a variable. Check also ExtendedRemoteFileInfo option below.

Related Topics: Integration Services Variables, Add Variable

ExtendedRemoteFileInfo (1.2 SR-2)

Indicates whether ResultVariable contains array of file name or extended file information. This property has the options listed in the following table.

Value	Description
True	The ResultVariable contains array of IFileInfo interface objects.
False	The ResultVariable contains array of file names.

Remark(s):
Not visible in the task user interface.

IsRemoteWildcard (1.2 SR-2)

Indicates whether the remote path is a wildcard. This property has the options listed in the following table.

Value	Description
True	The remote path is a wildcard.
False	The remote path is explicit specification.

Remark(s):
Not visible in the task user interface.

Overview

Script Task Plus is 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 ability to link to a common script code. Also the standard script task doesn't provide a way to expose user interface. We have implemented prebuilt user interface applied with attributes. The prebuilt user interface is so useful, it is in fact used to implement the user interface for the other components of the library. By resolving these shortcomings, we reduce the need for you to implement custom SSIS tasks.

The task dialog is very similar to the standard Microsoft Script Task dialog and has the following additional features:

• Initialize page - select 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 SSIS script to external file (.stx).
• Link Script - link to SSIS script from external file (.stx). The link can be removed by pressing the button again.

Requires SQL Server 2005 SP2.

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 property grid view and is available by selecting Initialize list item in CozyRoc Script Task dialog. We urge to familiarize yourself with PropertyGrid class because this is the class used for presentation and specified attributes below are related directly to it.

Check the samples below how to implement your first script task parameters.

How to start?

• Go to Toolbox and open Choose Toolbox Items ... dialog. Press SSIS Control Flow Items tab and select Script Task Plus.

  Add Reference

• Drag-in Script Task Plus into your control flow package and open Design Script IDE.
• Add reference in the script project to CozyRoc.SSISPlus

  Add Reference

• Include Imports CozyRoc.SqlServer.SSIS.Attributes in script. It is also recommended to include Imports System.Drawing.
  

  Imports System.Drawing
  Imports CozyRoc.SqlServer.SSIS.Attributes
  
  Public Class ScriptMain

• Now you are ready to define your first Script Task Parameter.

Simple Script Task Parameter

• Open Design Script IDE and define a bool property like the following:
  

  Property Bull() As Boolean
      Get
          Bull = m_bull
      End Get
      Set(ByVal value As Boolean)
          m_bull = value
      End Set
  End Property
  
  Private m_bull As Boolean

• Save, Build code and close IDE. Select Initialize list item and you will see Bull parameter.

Predefined list parameter

• Open Design Script IDE and define property attributed with List attribute. Use attribute constructor with array parameter. This is your predefined list.
  

  <List(New Object() {"USA", "Japan", "Panama"})> _
  Property Country() As String
      Get
          Country = m_country
      End Get
      Set(ByVal value As String)
          m_country = value
      End Set
  End Property
  
  Private m_country As String

Flexible list parameter

• To define dynamic list controlled by your script, you have to attribute your property with List attribute with constructor where you specify the class method returning the list.
  

  <List("GetAnimal")> _
  Property Animal() As String
      Get
          Animal = m_animal
      End Get
      Set(ByVal value As String)
          m_animal = value
      End Set
  End Property
  
  Public Function GetAnimal() As Object()
      If Me.Mammal
          GetAnimal = ...
      Else
          GetAnimal = ...
  End If
  End Function
  
  Private m_animal As String

Text dialog parameter

• Define property attributed with TextDialog. Attribute parameter title is the dialog title.
• If you want to validate entered text before dialog is closed, provide second attribute parameter validateMethod.
  

  <TextDialog("Enter a string", "ValidateString")> _
  <Description("This is a dialog string parameter.")> _
  Property DialogString() As String
      Get
          DialogString = m_string
      End Get
      Set(ByVal value As String)
          m_string = value
      End Set
  End Property
  
  Public Function ValidateString(ByVal value As String, ByRef errMessage As String) As Boolean
      Dim result As Boolean = True
  
      If value <> "Hello World" Then
          result = False
          errMessage = "Expected: Hello World"
      End If
  
      ValidateString = result
  End Function

Package connection parameter

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

  ReadOnly Property ConnectionType() As String()
      Get
          ConnectionType = New String() {"ADO.NET"}
      End Get
  End Property
  
  <Connection("ConnectionType")> _
  Property Connection() As String
      Get
          Connection = m_connection
      End Get
      Set(ByVal value As String)
          m_connection = value
      End Set
  End Property
  
  Private m_connection As String

• If you would like not to show ConnectionType property, specify System.ComponentModel.Browsable attribute to false.

Package Variable parameter

• Define property attributed with Variable attribute.
  

  <Variable()> _
  Property Variable() As String
      Get
          Variable = m_variable
      End Get
      Set(ByVal value As String)
          m_variable = value
      End Set
  End Property

SQL query list parameter

• To implement a parameter, whose list is a result of SQL query, you must attribute a property with SqlStatement attribute. The attribute's base constructor parameters are connection property and property containing SQL statement. Check sample code below for displaying a list of databases and a list of tables.
  

  <SqlStatement("Connection", "SqlDatabases")> _
  Property Database() As String
      Get
          Database = m_database
      End Get
      Set(ByVal value As String)
          m_database = value
      End Set
  End Property
  
  ReadOnly Property SqlDatabases() As String
      Get
          SqlDatabases = "SELECT name FROM sys.databases"
      End Get
  End Property
  
  <SqlStatement("Connection", "Database", "SqlTables", Nothing, "GetTables")> _
  Property Table() As String
      Get
          Table = m_table
      End Get
      Set(ByVal value As String)
          m_table = value
      End Set
  End Property
  
  ReadOnly Property SqlTables() As String
      Get
          SqlTables = "SELECT S.name, T.name FROM sys.tables AS T INNER JOIN sys.schemas AS S on T.schema_id = S.schema_id"
      End Get
  End Property
  
  Public Function GetTables(ByVal rows As DataRowCollection) As ArrayList
      Dim result As ArrayList = New ArrayList()
  
      For Each row As DataRow In rows
          result.Add(String.Format("[{0}].[{1}]", row(0), row(1)))
      Next
  
      GetTables = result
  End Function

Custom editor parameter

• To implement your own custom editor, you have to attribute a property with standard System.ComponentModel.Editor attribute. Check sample code below for implementation of file open dialog editor:
  

  '
  ' Implements File Open Dialog Editor.
  ' 
  Class FileEditor
      Inherits UITypeEditor
  
      ' Methods
      Public Sub New()
      End Sub
  
  
      Public Overrides Function GetEditStyle(ByVal context As ITypeDescriptorContext) As UITypeEditorEditStyle
          Return UITypeEditorEditStyle.Modal
      End Function    ' GetEditStyle
  
  
      Public Overrides Function EditValue( _
          ByVal context As ITypeDescriptorContext, _
          ByVal provider As IServiceProvider, _
          ByVal value As Object) As Object
  
          If context.PropertyDescriptor.Name <> "TargetFile" Then
              Return value
          End If
  
          Using dialog As OpenFileDialog = New OpenFileDialog()
              If dialog.ShowDialog() = DialogResult.OK Then
                  value = dialog.FileName
              End If
          End Using
  
          Return value
      End Function
  End Class   ' FileEditor
  
  ...
  
  <Editor(GetType(FileEditor), GetType(UITypeEditor))> _
  Property TargetFile() As String
      Get
          TargetFile = m_targetFile
      End Get
      Set(ByVal value As String)
          m_targetFile = value
      End Set
  End Property
  
  Private m_targetFile As String

How to provide category and description for parameter?

• Category and description are provided with standard System.ComponentModel attributes Category and Description.
  

  <Connection("ConnectionType")> _
  <Category("General")> _
  <Description("Select connection you would like to use.")> _
  Property Connection() As String
      Get
          Connection = m_connection
      End Get
      Set(ByVal value As String)
          m_connection = value
      End Set
  End Property

How to sort parameters?

• To sort parameters according to custom defined order, specify SortProperties attribute on the parameters class.
  

  <SortProperties(New String() {"Connection", "TargetFile", "Database"})> _
  Public Class ScriptMain
  ...

How to filter parameters?

• To filter parameters based on current state, specify FilterProperties attribute on the parameters class. Specify method name as attribute parameter. Specified method is dynamically called every time class parameters are refreshed.
  

  <FilterProperties("GetProperties"})> _
  Public Class ScriptMain
  
  ...
  
  Public Function GetProperties() As String()
      Dim result As New ArrayList
  
      Call result.Add("Action")  ' Action is always included.
  
      If Me.Action = "Upload File" Then
          Call result.Add("SourceFile")
      ElseIf Me.Action = "Download File" Then
          Call result.Add("TargetFolder")
      End If
  
      Return CType(result.ToArray(GetType(String)), String())
  End Function

  You may also check the sample script Http Upload/Download Task.

How to validate parameters?

• To validate parameters, specify ValidateProperties attribute on the parameters class. Specify method name as attribute parameter. Specified method will be called when user presses OK in the dialog.
  

  <ValidateProperties("Validate"})> _
  Public Class ScriptMain
  
  ...
  
  Public Function Validate(ByRef errMessage As String) As Boolean
      Dim result As Boolean
  
      result = False
  
      If Me.FromSender = "" Then
          errMessage = "Please specify FromSender."
      ElseIf Me.ToRecipient = "" Then
          errMessage = "Please specify ToRecipient."
      ElseIf Me.Subject = "" Then
          errMessage = "Please specify Subject."
      ElseIf Me.Message = "" Then
          errMessage = "Please specify Message."
      Else
          result = True
      End If
  
      Validate = result
  End Function

  You may also check the sample script Send Mail Task.

Overview

Database Partitions is SSIS control flow task for creating and maintaing SQL Server 2005 partitions. Microsoft SQL 2005 introduced table partitioning feature for handling large amounts of data. The feature is supported through proprietary SQL syntax extension. While the extension is functionally complete, many database administrators find that it requires too much manual intervention. In order to aid common repetitive table partitioning tasks, we designed a component with four operations:

• Create partition
• Insert partition
• Remove partition
• Combine partitions

Each operation has user interface to improve usability and minimize the learning. The task configuration consists of General and Partition Operation pages.

Setup

Use the Operation page of the Database Partitions Task Editor dialog to configure the partition operation you would like to perform.

Options

Action

Specify task action. This property has the options listed in the following table.

Value	Description
Create Partition	This operation creates a new staging partition table with same structure as the target table, without the constraints. This operation is usually executed before you start loading your data and it is needed because SQL server doesn't provide command with this functionality. Selecting this value displays the dynamic options TargetTable, PartitionTable.
Insert Partition	This operation inserts staging partition table into target partitioned table. The partition table should contain slice of data only for selected partition key. The operation performs following sub-steps: Find where selected partition table fits in the target table, modifying the partition function if needed. Combine overlapping data in the partition table and target table. Switch-out and remove old partition data. Set constraints and indexes on selected partition table. Switch-in the partition table. If you are familiar with the SQL syntax for partitioning, it will roughly translate to: ALTER TABLE PartitionTable SWITCH TO TargetTable PARTITION PartitionKey This operation is executed usually after you finish loading data, to make your staging area part of your target table. Selecting this value displays the dynamic options TargetTable, PartitionTable, PartitionKey.
Remove Partition	This operation removes partition from selected target partitioned table. You have to select partition key of the partition you want removed from target table. PartitionTable option is optional. If it is specified, the task will create a table with the removed partition data. You can use this option, when you need to roll-off and backup the data. Selecting this value displays the dynamic options TargetTable, PartitionTable, PartitionKey.
Combine Partitions	This operation is a maintenance operation used to combine multiple smaller sequential partitions into a bigger partition. This operation is important for your data warehouse because it keeps your partitions manageable and easier to backup at later time. This operation is executed usually in a maintenance workflow at the end of a business period (week, month, quarter). Selecting this value displays the dynamic options TargetTable, Granularity.

TargetTable

Select target partitioned table.

PartitionTable

Specify partition table name.

PartitionKey

Specify partition key. Target table partition column type is the type of the key.

Granularity

Specify a sequence of how many small partitions you want to be combined. If you partition your data by day and you have data arriving every day, by the end of the month you will have 30 partitions. To make you partitions contain weekly data, you have to specify 7.

Overview

Data Flow Task Plus is SSIS control flow task and it is an extension of the standard Microsoft Data Flow Task. The features we have implemented are very similar to the ones we have already implemented for Script Task Plus. Data Flow Task Plus allows exporting of data flow logic and reuse in other packages. Keeping your SSIS data flow logic separate from your package, helps you keep track of the changes independently in your source control system. Data Flow Task Plus provides also customizable user interface for setup of your data flow. You customize the data flow setup dialog by implementing an SSIS script, part of the data flow task. We believe these features will help you be more more productive and allow you to componentize your common data flow logic.

The task setup is very similar to the Script Task Plus dialog. It has the following features:

• General page - specifies data flow task name and description.
• Initialize page - select data flow initialization parameters. These data flow parameters will be initialized before the data flow is executed. The page configuration is prepared with attributes in the initialization script.
• Script page - specifies data flow task script, which is used for Initialize page customization.
• Expressions page - specifies standard SSIS expressions.
• Export Data Flow - export SSIS data flow to external file (.dfx).
• Link Data Flow - link to SSIS data flow from external file (.dfx). The link can be removed by pressing the button again.

Setup

The initialization and user interface for a data flow is defined by implementing properties on the script EntryPoint class. The documentation and samples of Script Task Plus for defining parameters is relevant to Data Flow Task Plus initialization setup, so we will not repeat it here. Here you will find description of DataFlowSetting attribute, used to apply parameter value to specific data flow component setting.

How to start?

• Go to Toolbox and open Choose Toolbox Items ... dialog. Press SSIS Control Flow Items tab and select Script Task Plus.

  Add Reference

• Drag-in Data Flow Task Plus into your control flow package and open Design Script IDE.
• Add reference in the script project to CozyRoc.SSISPlus

  Add Reference

• Include Imports CozyRoc.SqlServer.SSIS.Attributes in script. It is also recommended to include Imports System.Drawing.
  

  Imports System.Drawing
  Imports CozyRoc.SqlServer.SSIS.Attributes
  
  Public Class ScriptMain

• Now you are ready to define your first Data Flow Task Plus Parameter.

DataFlowSetting attribute

DataFlowSetting attribute defines a list of references to different component properties in the SSIS data flow. It defines the link between initialization parameter and specific SSIS data flow component setting. You can apply one initialization parameter to multiple data flow components and properties. Each reference is a text string with separate sections. Each section is separated with "." dot. Every reference starts with component, followed by "keyword" specifying setting type. Here is a list of sample DataFlowSetting attribute reference types, which are supported currently by Data Flow Task Plus component:

• DataReader Source.Property.PreCompile - set property PreCompile.
• DataReader Source.Connection.IDbConnection - set connection IDbConnection.
• DataReader Source.Column.Input.DataReader Input.DateKey - set input column DateKey in DataReader Input input.
• DataReader Source.Column.Output.DataReader Output.DateKey - set output column DateKey in DataReader Output output.
• DataReader Source.Column.OutputExternal.DataReader Output.DateKey - set external output column DateKey in DataReader Output output.

where DataReader Source is a reference to data flow component.

In SSIS different data flow elements like components, properties, connections, columns can be referenced either by name or ID. DataFlowSetting attribute reference supports both.

Data Flow property setting

• Sample script to setup CommandTimeout property of DataReader Source component.
  

  <DataFlowSetting(New String() {"DataReader Source.Property.CommandTimeout"})> _
  Public Property CommandTimeout() As Integer
      Get
          CommandTimeout = m_commandTimeout
      End Get
      Set(ByVal value As Integer)
          m_commandTimeout = value
      End Set
  End Property

Data Flow connection setting

• Sample script to setup IDbConnection connection of DataReader Source component.
  

  <Connection("ConnectionType")> _
  <DataFlowSetting(New String() {"DataReader Source.Connection.IDbConnection"})> _
  Public Property Connection() As String
      Get
          Connection = m_connection
      End Get
      Set(ByVal value As String)
          m_connection = value
      End Set
  End Property
  
  <Browsable(False)> _
  Public ReadOnly Property ConnectionType() As String
      Get
          ConnectionType = "OLEDB"
      End Get
  End Property

Data Flow column setting

• Sample script to setup column with ID 3018 in DataReader Output output of DataReader Source component.
  

  <DataFlowSetting(New String() {"DataReader Source.Column.Output.DataReader Output.3018"})> _
  Public Property Column() As String
      Get
          Column = m_column
      End Get
      Set(ByVal value As String)
          m_column = value
      End Set
  End Property