Note 2: this method could be a bit inefficient if your file collection contains thousands of files and you only need two of them. Note 1: to gain some performance I should have changed the Foreach Loop File Enumerator wildcard from *.* to *.csv *\.csv$ (all csv files that end with a number in the filename) ' Check if it is match and return that value (boolean) to the SSIS variableĭts.Variables("User::PassesRegexFilter").Value = FileFilterRegex.IsMatch(FileName)Ĭonnect the Script Task to the Data Flow Task and add an expression that does the real = trueįor testing the result, I added an other Script Task below the Data Flow Task that logs all files. ' Create a regex object with the pattern from the SSIS variableĭim FileFilterRegex As Regex = New Regex(Dts.Variables("User::RegexFilter").Value.ToString()) ' Get the filename from the complete filepathĭim FileName As String = Path.GetFileName(Dts.Variables("User::FilePath").Value.ToString()) Inherits .ScriptTask.VSTARTScriptObjectModelBase
#DATA TOOLBAR REGEX FILTER CODE#
' VB.Net Code for filtering filenames with Regex Check if it is match and return that value (boolean) to the SSIS variableĭts.Variables.Value = FileFilterRegex.IsMatch(FileName) ĭts.TaskResult = (int)ScriptResults.Success Create a regex object with the pattern from the SSIS variable Get the filename from the complete filepath Success = .DTSExecResult.Success,įailure = .DTSExecResult.Failure Public partial class ScriptMain : .ScriptTask.VSTARTScriptObjectModelBase C# Code for filtering filenames with Regex I will use two extra variables in this example: RegexFilter (string) for storing the regular expression and PassesRegexFilter (boolean) for indicating whether the filename passes the regular expression filter.Īdd a Script Task in front of the Data Flow Task and give it a suitable name.Įdit the Script Task and add the FilePath and RegexFilter as ReadOnlyVariables and the PassesRegexFilter as ReadWriteVariable.Ĭopy the following script to the Script Task. The filter is *.* so all files will be returned. *įor this case I will use a standard Foreach File Enumerator that fills a variable FilePath. *update: Now also available as Custom Foreach Enumerator. You could achieve the same result with a Script Task. See/vote this request at Microsoft Connect.Īt the moment I'm working on a custom File Enumerator with regular expression support, but for those who don't want to wait or don't want to use custom components. The standard Foreach Loop File Enumerator only has a wildcard filter, but in some cases a regular expression filter would be more useful.
0 kommentar(er)
