SSIS archive and rename all the files in a folder in loop

Archive and rename all the text files in a folder in loop
Download Package
ArchiveAndRename.dtsx

If one of the task in your SSIS package is to move the source text files to an archive folder after its processed then this can be done through the script task in SSIS.

Steps involved :
  1. create a variable in SSIS package : right click anywhere in your package and click on variables --> create a variable  as "vFileName" with string as datatype
  2. Create a text file connection with the path to connect the source text file --> define the columns ( the connection path will disappear once its executed as it will use the expressions for connection string in next step)


    • ssis create expressions for connection in ssis :  right click on text file in the connections area --> click on properties  --> click on expressions --> select the user::variable in the left pane --> select "connection string" at the right pane --> click "Ok"


  3. Drag the "foreach loop container" --> right click -->; click on properties --> click on variable --> select "vFileName"
  4. Drag a dataflow or any other transformations to process the text file ( here this package is using a dummy dataflow)
  5. Last task is to archive the source file using a "script task" to a different folder called ARCHIVE and rename the filename with date.

2 comments:

  1. can i have the similar frameworks

    ReplyDelete
  2. Such a simple, elegant solution. This is great.

    ReplyDelete