Library references

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

ScriptMain

' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2007 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.IO Imports System.Collections Imports System.ComponentModel Imports System.Drawing.Design Imports System.Windows.Forms Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes ' ' Implements Folder Selection Editor. ' Class FolderEditor 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 <> "FileFolder" Then Return value End If Using dialog As FolderBrowserDialog = New FolderBrowserDialog() If dialog.ShowDialog() = DialogResult.OK Then value = dialog.SelectedPath End If End Using Return value End Function End Class ' FolderEditor ' ' Retrieves file list for specified folder and filter. ' Demonstrates also CozyRoc UI editor for interactively selecting folder and other parameters. ' 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() Dim result As ArrayList Dim files() As String Dim file As String result = New ArrayList Try ' Get file list. files = Directory.GetFiles(Me.FileFolder, Me.FileFilter) ' Build result list. You can filter further filter the list with additional criterias. For Each file In files Call result.Add(file) Next Catch ex As Exception ' Failed to retrieve file list. Call Dts.Events.FireError(1001, "GetFileListTask", ex.Message, "", 0) End Try ' Set result variable. Dts.Variables("FileList").Value = result Dts.TaskResult = Dts.Results.Success End Sub <Editor(GetType(FolderEditor), GetType(UITypeEditor))> _ <Description("Select a folder from which you would like to retrieve file list.")> _ Public Property FileFolder() As String Get FileFolder = m_fileFolder End Get Set(ByVal value As String) m_fileFolder = value End Set End Property <Description("Select file list filter.")> _ Public Property FileFilter() As String Get FileFilter = m_fileFilter End Get Set(ByVal value As String) m_fileFilter = value End Set End Property Private m_fileFolder As String Private m_fileFilter As String End Class