Thursday, October 8, 2009

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

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.
Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...