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
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.

8 comments:

  1. can i have the similar frameworks

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

    ReplyDelete
  3. Thanks for one's marvelous posting! I definitely enjoyed reading it, you might be a great author.I will make certain to bookmark your blog and will often come back very soon. I want to encourage you to definitely
    continue your great posts, have a nice weekend!

    ReplyDelete
  4. I am really pleased to glance at this website posts which contains plenty of helpful
    information, thanks for providing these data.

    ReplyDelete
  5. Hey very interesting blog!

    ReplyDelete
  6. Just desire to say your article is as astonishing. The clearness
    in your post is simply cool and i can assume you are an expert on this subject.
    Fine with your permission let me to grab your feed to keep updated
    with forthcoming post. Thanks a million and please keep up the gratifying work.

    ReplyDelete
  7. Thanks for the tips guys. They were all great. I have been having issues with being fat both mentally and physically. Thanks to you guys i have been showing improvements. Do post more.Protective fabrics

    ReplyDelete
  8. Thanks for the tips guys. They were all great. I have been having issues with being fat both mentally and physically. Thanks to you guys i have been showing improvements. Do post more.Protective fabrics

    ReplyDelete

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 t...

Contributors