Monday, January 23, 2012

SSIS Expression To Append Time and Date with File Name Dynamically

Use Following expression to append date and time at the time of package execution with the file name.
[code]
"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"
[/code]
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:
[code]
@[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"
[/code]

Hope it Helps :)

1 comment:

SSIS: Delete Empty Error Log Files With Dynamic Names | SSIS and Sql Server Journey said...

[…] 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 […]