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: 99126 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: 99126 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: 246723 More actions · Answer 6

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.