Alex Rivera | Logout

How can I set an expression to the FileSpec property on Foreach File enumerator?

Asked 2012-11-06T17:16:24.300
10

I'm trying to create an SSIS package to process files from a directory that contains many years worth of files. The files are all named numerically, so to save processing everything, I want to pass SSIS a minimum number, and only enumerate files whose name (converted to a number) is higher than my minimum.

I've tried letting the ForEach File loop enumerate everything and then exclude files in a Script Task, but when dealing with hundreds of thousands of files, this is way too slow to be suitable.

The FileSpec property lets you specify a file mask to dictate which files you want in the collection, but I can't quite see how to specify an expression to make that work, as it's essentially a string match.

If there's an expression within the component somewhere which basically says Should I Enumerate? - Yes / No, that would be perfect. I've been experimenting with the below expression, but can't find a property to which to apply it.

(DT_I4)REPLACE( SUBSTRING(@[User::ActiveFilePath],FINDSTRING( @[User::ActiveFilePath], "\", 7 ) + 1 ,100),".txt","") > @[User::MinIndexId] ? "True" : "False"

ssis sql-server-2012
Edit
Report

1 Answer

16

Here is one way you can achieve this. You could use Expression Task combined with Foreach Loop Container to match the numerical values of the file names. Here is an example that illustrates how to do this. The sample uses SSIS 2012.

This may not be very efficient but it is one way of doing this.

Let's assume there is a folder with bunch of files named in the format YYYYMMDD. The folder contains files for the first day of every month since 1921 like 19210101, 19210201, 19210301 .... all the upto current month 20121101. That adds upto 1,103 files.

Let's say the requirement is only to loop through the files that were created since June 1948. That would mean the SSIS package has to loop through only the files greater than 19480601.

Files

On the SSIS package, create the following three parameters. It is better to configure parameters for these because these values are configurable across environment.

  • ExtensionToMatch - This parameter of String data type will contain the extension that the package has to loop through. This will supplement the value to FileSpec variable that will be used on the Foreach Loop container.

  • FolderToEnumerate - This parameter of String data type will store the folder path that contains the files to loop through.

  • MinIndexId - this parameter of Int32 data type will contain the minimum numerical value above which the files should match the pattern.

Parameters

Create the following four parameters that will help us loop through the files.

  • ActiveFilePath - This variable of String data type will hold the fi

answered 2012-11-06T20:05:03.023

Your Answer