ssis foreach loop Traversing Folders - need to populate variable with the current folder path the loop is on

• jerryminor 64917

  Grasshopper

  Points: 14

  More actions

  October 23, 2018 at 1:00 pm

  #372283

  While traversing folders in a foreach loop, I need to have a variable set with the path of the file the loop is currently on.
  The variable will be used to build an archive path in the next step where I will move the processed files to a sub-folder in the current directory.
  I have found so many posts on this and similar topics but each post is only a piece of the puzzle.
  The ultimate response would be for someone to develop or point me to a step by step document starting with building the solution or project.
  My folder structure looks like:
  C:\test\Folder_01\Archive
  C:\test\Folder_02\Archive
  I want to process files starting at C:\test and traverse to the lower folder at the second level.
  Once I process the file, I want to Move it to the Archive folder below in the same folder it came from, and rename the file.
  The foreach collection points to C:\test and restricts the files to *BKP.xls
  Once the file is moved to the Archive folder it will be renamed to *BKPA.xls so that it will not be processed again.
  Having the file path in a variable should help me in the File System Task derive the Archive folder name..something like @[User::FolderPath] + "\\Archive"
  A complete example is desperately needed and will be greatly appreciated.

• Mike01

  SSChampion

  Points: 11412

  More actions

  October 29, 2018 at 1:41 pm

  #2011589

  have you tried anything or just looking for someone to give you the solution?

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  October 29, 2018 at 2:09 pm

  #2011593

  I would use a Script Task instead of a File System task, and use C# code to figure out where the Archive folder is and to move the file.

• Mike01

  SSChampion

  Points: 11412

  More actions

  October 29, 2018 at 2:26 pm

  #2011596

  try this

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/