SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

SSIS Expression To Append Time and Date with File Name Dynamically

on January 24, 2012

Use Following expression to append date and time at the time of package execution with the file name.

"D:\output\LogFile"
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"

Evaluated Expression would look some thing like this:

D:\output\LogFile2012-01-17#12-11-54.txt

Using Variable:

@[User::OutputFileLocation]  : Variable to hold file location

@[User::OutputFileName]  : Variable to hold file name

Expression:

@[User::OutputFileLocation]
+ @[User::OutputFileName]
+ (DT_WSTR, 50)(DT_DBDATE)GETDATE()+"#"
+(DT_STR,2,1252)DATEPART("hh",GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+"-"
+(DT_STR,2,1252)DATEPART("ss",GETDATE())
+".txt"

Hope it Helps 🙂

Advertisements

5 responses to “SSIS Expression To Append Time and Date with File Name Dynamically

  1. […] a text file. In my case, I had to create the text file for each run hence I created a file with dynamic name. SSIS would create the file even when there is no error. Now we need to delete the file in case the […]

  2. T-Bash says:

    This is exactly what i needed.

    Sure as hell helped and great post.

  3. Srinivas says:

    Hi All,
    i want to ZIP the file with same file name.

    Please help me.

  4. Srinivas says:

    @[User::OutputFileLocation]
    + @[User::OutputFileName]
    + (DT_WSTR, 50)(DT_DBDATE)GETDATE()+”#”
    +(DT_STR,2,1252)DATEPART(“hh”,GETDATE())+”-”
    +(DT_STR,2,1252)DATEPART(“mi” ,GETDATE())+”-”
    +(DT_STR,2,1252)DATEPART(“ss”,GETDATE())
    +”.txt”

    For the above file name like (ABC_20140528_132950.txt)

    I want to Zip this file with same file name(ABC_20140528_132950.txt.zip) in different location.

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

%d bloggers like this: