Renaming JUST THE EXTENSION OF multiple files in a folder using SSIS

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  September 6, 2022 at 6:23 pm

  #4084582

  Hi,

  I have three files in a folder - SourceFolder

  ABC.edifact

  DEF.edifact

  NJF.edifact

  I need to rename these three or more files which drop into the above folder every day to

  ABC.txt

  DEF.txt

  NJF.txt

  The folder should remain the same, I just need to rename the extension of all files in a folder.

  I tried foreachloop container and file system task but not able to achieve anything

  Can anyone give a step by step approach?

  Thanks in advance

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 6, 2022 at 7:01 pm

  #4084608

  Chances are, a For Each Loop Container is not going to work very well.   It's not easily parameterized for the values needed.   It would actually be easier to write a Script Task in Visual Basic to do the job.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  September 7, 2022 at 8:55 am

  #4084798

  I'd also use a Script Task, though I'm more inclined to use C#.

  Alternatively, run a DOS one-liner with an Execute Process task. The command would be something like this:

  

  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

• Ed B

  Hall of Fame

  Points: 3666

  More actions

  September 7, 2022 at 2:33 pm

  #4084971

  A for each look can work, although a script does seem much simpler.

  The Foreach File Enumerator uses a parameter for the source directory

  : @[$Package::SourceDir] and returns the file name only with no suffix into a variable. @[User::FileName]

  

  

  Using variables for the new and old suffix you can build the source and destination variables for the File System Task. The File System Task is set to rename file with IsDestinationPathVariable and IsSourcePathVariable set to true and the appropriate variables assigned. I included the full stop/period in the suffix variables to save adding it to the expressions.

  --[User::FullSourcePath] 
  @[$Package::SourceDir] + @[User::FileName] + @[User::OldSuffix]
  
  -- [User::FullDestPath]
  @[$Package::SourceDir] + @[User::FileName] + @[User::NewSuffix]