How to set Files property of Enumeration Configruation in Foreach Loop Editor

Question

How to set Files property of Enumeration Configruation in Foreach Loop Editor

Weegee2017

SSC Eights!

Points: 994
More actions
March 23, 2020 at 11:49 am

#3736191

|Hi there
I have a For Loop container which is processing a set of files.
Now there are a certain number of files which will be processed with a
certain file path as follows:
SVPP_ROYA_00001_00021_20200225_01.csv
SVPP_ROYA_00001_00021_20200225_02.csv
SVPP_ROYA_00001_00021_20200225_03.csv
I want to set the property in The Editor for Enumerator Configuration (Files),
so it would be as follows:
SVPP_ROYA_00001_00021_20200225_**.csv
I have a SSIS Variable referred to the above as Dts.Variables["User::FileFullPath"]
How can i set the Files path of the For Loop Enumerator to use my variable [User::FileFullPath]?
Can this be done in Code in a scropt task?
For example
\Package\Foreach Loop Container.Properties[ForEachEnumerator].Files = Dts.Variables["User::FileFullPath"]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

You can use an expression to set this property. As shown below, you can use a hard-coded expression to define this. If you already have a variable with this file pattern, you'd substitute that variable instead of the hard-coded expression.

foreach

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Thom A SSC Guru Points: 99130 More actions · Answer 2

If you're setting the value of the Folder, then define the Expressions options to set the value of the Directory. If you want to set the value of Files, then set use the Expressions to set the value of FileSpec.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee2017 SSC Eights! Points: 994 More actions · Answer 3

Hi Tim

Thanks for you reply

Ok so I can set the property for FileSpec to @[User::FileImportMask] ie SVPP_ROYA_00001_00021_20200225_**.csv

Now I can I loop through and capture each of my file names ] which match the file spec?

SVPP_ROYA_00001_00021_20200225_01.csv

SVPP_ROYA_00001_00021_20200225_02.csv

SVPP_ROYA_00001_00021_20200225_03.csv

What I want to do is to load them into an audit table

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

That's correct, except that you don't need two asterisks the file spec. A single asterisk will match multiple characters, so you could simply use SVPP_ROYA_00001_00021_20200225_*.csv.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Thom A SSC Guru Points: 99130 More actions · Answer 5

If you want to match a single character, so match the value SVPP_ROYA_00001_00021_20200225_03.csv but not SVPP_ROYA_00001_00021_20200225_9.csv or SVPP_ROYA_00001_00021_20200225_100.csv then use ? as the wildcard operator, which represents a single character, not many: SVPP_ROYA_00001_00021_20200225_??.csv

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246961 More actions · Answer 6

As you are loading multiple files, are you concerned about the order in which they are loaded?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin