Post reply

Issue with appending date and timestamp to file name

  • November 25, 2019 at 9:01 pm

    #3700763

    I have an SSIS expression where it appends  20191125155915.xlsx to filename

     

    @[User::FilePath] + "\\" + "DBAFullPro_"+(DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) +RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2)+ RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2)+ RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2)+RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)+".xlsx"

     

    I am using file system task to rename original file name to _timestamp. Once this is renamed. it will move the renamed file to network share path.

     

    suppose if there is any delay in the process, its not looking for correct file because of seconds. it immediately changing the seconds and package is failing. error - cannot find the file in the path.

    is there any way to overcome this?

  • November 25, 2019 at 9:12 pm

    #3700768

    Sure. Create a string variable and use an Expression task to set its value (using the code you pasted).

    The rename and move can then reference the variable.

    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

  • November 25, 2019 at 9:18 pm

    #3700770

    I've also used values like the package StartTime which aren't going to change within a single execution of a package.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 25, 2019 at 9:40 pm

    #3700788

    i have a string variable where it holds the datetime stamp value. using the same in move too.

     

    in between rename and move file system task. i have created dummy script task to delay the process for 5 sec. in this case file name datetimestamp changed after the delay process.

    Move task is failed as the filename didn't find

  • November 25, 2019 at 9:59 pm

    #3700793

    Please reread my post. I said use an Expression task to set the value of a variable. Once set in this way, the value will remain static.

    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

  • November 26, 2019 at 5:02 pm

    #3701160

    It didn't worked. I have an excel template where i copy the format from source to destination, once data loaded into the destination file, i am renaming it with file system task as rename file operation

    example - the original file name is detailreport.xls , i have renamed that to 001detailreport_20191125.xls.

    next task is to move this file to fileshare(network)  and same file has to move to archive locally.

    for moving to file share i have hardcoded the variable path and used file system task as copy file operation from renamed fiel location to file share location.

    next is the same file is moved to archive locally using move file operation. here i am getting confused how i can add hours min and sec to file name that is already moved to both locations?

  • November 26, 2019 at 5:58 pm

    #3701173

    I'm sorry, but I don't know how to write it any more clearly. To summarise:

    1. Create a variable
    2. Set the variable to the new file name using an Expression task (do not add an expression to the definition of the variable itself).
    3. Use the variable when you perform the rename and the move.

    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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply