CODEWITHSUNDEEP
variablestimessis
Phteven
Phteven

Reputation: 139

Capturing and storing the time as a string in SSIS

I have an SSIS package that outputs csv files. The flat file connection managers use connection string expressions with variables to construct the file name as the task is processed, they all end with the date and time turned into a gapless string separated by an underscore, for example;

\\server1\departments\Management Information\GDPR\Watch Folder Files\Marketing_Consent_20180726_1400.csv

The variable expression I use to capture the time from the date and turn it into a string is as follow;

SUBSTRING((DT_WSTR, 25) (DT_DATE) GETDATE(), 12, 2 ) + 
SUBSTRING((DT_WSTR, 25) (DT_DATE) GETDATE(), 15, 2 )

The flaw in this is that it's using GETDATE(). When the process runs, if the time ticks over by a minute after the file is created, the later file system tasks that are suppose to move the files aren't doing so because it's looking for a file that ends with 1401.csv when the actual file was created ending with 1400.csv.

Can I capture the time at the outset of the package but then store it and keep using that same timestamp throughout the package instead of it constantly being refreshed by calling the GETDATE() function?

Upvotes: 0

Views: 313

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Yes, at the outset of the package use a script task or Execute SQL task to store the value of GETDATE() in a package variable.

Upvotes: 2

Related Questions