For each loop through complex filenames

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  March 1, 2018 at 10:54 am

  #403149

  Hello

  There may be a fundamentally different approach required here - not sure what though!

  I have a file structure that will always be of the form:

  IN_<reference>_DDMMCCYY_post.txt

  reference will differ every time
  DDMMCCYY is a date and should also differ every time - although, it will always be 8 digits in this format

  There will be a number of files of this format within a folder
  There could be other files of a different format

  I only want to read in the files of this format then write the data to a table

  So, I was think of an SSIS For each loop through the folder looking for file of this form
  Within the loop container, it will write the data

  Really struggling with the expression though

  I was hoping something like: "IN_*_????????_post.txt" might work
  It validates but doesn't return anything

  These expressions work:

  "IN_*.txt"
  "*_????????_*.txt"

  Any thoughts?

  Thanks

  - Damian

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  March 1, 2018 at 11:07 am

  #1981686

  DamianC - Thursday, March 1, 2018 10:54 AM

  Hello

  There may be a fundamentally different approach required here - not sure what though!

  I have a file structure that will always be of the form:

  IN_<reference>_DDMMCCYY_post.txt

  reference will differ every time
  DDMMCCYY is a date and should also differ every time - although, it will always be 8 digits in this format

  There will be a number of files of this format within a folder
  There could be other files of a different format

  I only want to read in the files of this format then write the data to a table

  So, I was think of an SSIS For each loop through the folder looking for file of this form
  Within the loop container, it will write the data

  Really struggling with the expression though

  I was hoping something like: "IN_*_????????_post.txt" might work
  It validates but doesn't return anything

  These expressions work:

  "IN_*.txt"
  "*_????????_*.txt"

  Any thoughts?

  Thanks

  I'd stick with your simple version ("IN_*.txt") on the FEL and add a script task as step 1 within your FEL to set a package-scoped variable to indicate whether to process the file (YES/NO, 1/0, TRUE/FALSE or whatever).
  Use precedence constraints after the script task to control whether or not the 'current' file gets processed.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  March 1, 2018 at 11:13 am

  #1981690

  Ah right, yes that makes sense Phil

  About to leave for the day so i'll give that a go in the morning

  Much appreciated

  Damian.

  - Damian