Issue with appending date and timestamp to file name

  • 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?

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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?

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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