Help with SSIS ForEach File Loop Enumerator configuration

• BobMcC

  SSCrazy

  Points: 2260

  More actions

  June 22, 2010 at 11:35 am

  #141674

  I'm using an SSIS Foreach loop to enumerate the *.zip files in a directory.

  The trouble is it's returning files as if I used *.zip*

  In other words it's returning things like filename.zipxx which is exactly what I'm trying to avoid.

  Can anyone recommend a solution?

  Thanks,

  Bob McC

• vineet_

  SSC-Addicted

  Points: 475

  More actions

  June 22, 2010 at 3:16 pm

  #1183615

  I tried a work around for this:

  1) In the For Each Loop Variable Mappings map Index 0 to a string variable (user::filepathname)

  Set Folder Path

  Set Files: *.zip

  2) With in the For Each Container, ahead of all other tasks, add an Execute SQL Task. Connect it to any database.

  Set ResultSet to SingleRow

  In the Expressions Tab,

  Set expression for SQLStatementSource: "select CharIndex('.',reverse('"+ @[User::filepathname] +"'))"

  In the Result Set tab map Result Name = 0 and Variable Name = User::IntVariable (any integer variable declared)

  3) Add precedence constraint between the abpve SQL task and rest of the tasks in the For Each Container

  Set Evaluation Operation = Expression and Constraint

  Value = Success

  Expression: @IntVariable == 4

  Select Logical And

  4) Close all Save Run

  ------------------
  Why not ?