Format a Timestamp String Expression for a Filename in SSIS

Two useful methods for how to format a timestamp string  in SSIS – assign to a package variable via a Script task, or, via a regular expression. Both techniques are outlined.

The first option is to use the Script task and then use .Net code to format the timestamp as outlined here…

http://www.mssqltips.com/sqlservertip/2530/dynamically-name-text-files-in-sql-server-integration-services/.

Another simpler alternative for the .Net disinclined is to create a package variable called @Timestamp, then format an expression that evaluates to a string version of the current timestamp.  You can use the package start time, container start time or getdate() as a few options.     Then create your package filename variables and reference @Timestamp in the filename expressions.

The following code snippet will configure the timestamp string in the format yyyy_mm_dd_hr_min_sec based on the package start time system timestamp…


(DT_WSTR, 4)YEAR( @[System::StartTime])

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) MONTH( @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DAY( @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DATEPART( "hh", @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DATEPART( "mi", @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DATEPART( "ss", @[System::StartTime]),2)

If you choose to use getdate() however take care when referencing the same filename in more than one task in the package as the timestamp could shift between tasks and you will get a file not found error.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s