Library references

• System
• System.Data
• Microsoft.SqlServer.ScriptTask
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus
• System.Drawing

ScriptMain

' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.ComponentModel Imports System.Data Imports System.Collections Imports System.Drawing.Design Imports System.Windows.Forms Imports System.Windows.Forms.Design Imports CozyRoc.SqlServer.SSIS.Attributes ' ' Implements File Selection 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 Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() Dts.TaskResult = Dts.Results.Success End Sub <List(New Object() {1, 2, 3})> _ <Description("This is integer parameter.")> _ Property IntegerParameter() As Integer Get IntegerParameter = m_integer End Get Set(ByVal value As Integer) m_integer = value End Set End Property <List(New Object() {"A", "B", "C"})> _ <Description("This is list string parameter.")> _ Property ListString() As String Get ListString = m_string End Get Set(ByVal value As String) m_string = value End Set End Property Property StringParameter() As String Get StringParameter = m_string End Get Set(ByVal value As String) m_string = value End Set End Property <List("GetList")> _ Property DelegatedList() As String Get DelegatedList = m_string End Get Set(ByVal value As String) m_string = value End Set End Property Property BoolParameter() As Boolean Get BoolParameter = m_bool End Get Set(ByVal value As Boolean) m_bool = value End Set 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 <Variable()> _ Property Variable() As String Get Variable = m_variable End Get Set(ByVal value As String) m_variable = value End Set End Property <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 <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 <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 <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 <Browsable(False)> _ ReadOnly Property ConnectionType() As String() Get ConnectionType = New String() {"ADO.NET"} End Get End Property ReadOnly Property SqlDatabases() As String Get SqlDatabases = "SELECT name FROM sys.databases" End Get 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 GetList() As Object() GetList = New Object() {10, 20, 30} End Function 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 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 Private m_integer As Integer Private m_string As String Private m_bool As Boolean Private m_connection As String Private m_database As String Private m_table As String Private m_targetFile As String Private m_variable As String End Class