none
SSIS Rename and move Files

    Question

  • I have a situation I have to read zip files from the source unzip them and store data in datawarehouse and store the unzip files in archive folder.
    Every thing is running fine. The problem here is in one folder there are many zip files obviously with different names and one actual file in each zip archive  names but the actual file in each zip archive has same name. so after unzipping when i try to save them in archive folders the old files are always overwritten by the new one. The structure of source is

    1. One folder for each day

    2. Variable number of folders with in daily folder

    3. Variable number of zip files in each folder

    I have made the same structure at destination by using nested for each loop using fully qualified names.

    every thing is fine except avoiding this overwrite issue. I tried to change some setting in move file task tried to rename and move file by appending timestamp infront of each file but nothing is apparently working.

    I have used many different blogs on renaming and moving files but no success.

    Can someone help me out.

     

    Friday, November 13, 2009 3:46 PM

Answers

  • What is the time stamp u are using?
    Is it just the date or are you appending the time including the seconds in it??

    Use this expression to append the file name with the timestamp:

    (DT_WSTR,4)YEAR( GETDATE()  ) +(DT_WSTR,4)MONTH( GETDATE()  )+ (DT_WSTR,4)DAY( GETDATE()  ) + REPLACE((SUBSTRING((dt_wstr,30) GETDATE() ,12,8)),":","")
    Hope this helps !!
    Sudeep   |    My Blog
    • Marked as answer by grahan007 Monday, November 16, 2009 12:45 PM
    Friday, November 13, 2009 4:01 PM

All replies

  • What is the time stamp u are using?
    Is it just the date or are you appending the time including the seconds in it??

    Use this expression to append the file name with the timestamp:

    (DT_WSTR,4)YEAR( GETDATE()  ) +(DT_WSTR,4)MONTH( GETDATE()  )+ (DT_WSTR,4)DAY( GETDATE()  ) + REPLACE((SUBSTRING((dt_wstr,30) GETDATE() ,12,8)),":","")
    Hope this helps !!
    Sudeep   |    My Blog
    • Marked as answer by grahan007 Monday, November 16, 2009 12:45 PM
    Friday, November 13, 2009 4:01 PM
  • You can do that in a single step using File System task in control flow:

    http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html 

    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Friday, November 13, 2009 4:22 PM
  • What is the time stamp u are using?
    Is it just the date or are you appending the time including the seconds in it??

    Use this expression to append the file name with the timestamp:

    (DT_WSTR,4)YEAR( GETDATE()  ) +(DT_WSTR,4)MONTH( GETDATE()  )+ (DT_WSTR,4)DAY( GETDATE()  ) + REPLACE((SUBSTRING((dt_wstr,30) GETDATE() ,12,8)),":","")
    Hope this helps !!
    Sudeep   |    My Blog

    Thanks Sudeep
    Actually the ":" was the problem which I couldn't understand. in expression it was giving no error but when the package tries to write file in the file system doesn't support ":".

    Cheers
    Monday, November 16, 2009 12:49 PM