Issue with appending date and timestamp to file name

Question

Post reply

Issue with appending date and timestamp to file name

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
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?

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

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

drew.allen SSC Guru Points: 76944 More actions · Answer 2

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

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 5

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?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 6

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

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.