Showing posts with label filename. Show all posts
Showing posts with label filename. Show all posts

Friday, March 9, 2012

How to add date and time to Flat File Connection Manager

I have specified a name for my output file in the properties of my Flat File Connection but how can I add the date and time to that filename? If it is using expressions, what would be the expression and where exactly do I paste that expression?

Nice one:

Expression:

"FileNameBase_" + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()

Looks like this:

FileNameBase_2006-01-03_08:55:28

Place is on the Connection String property of the Flat File Connection Manager.

HTH,

K

|||

I have the following expression attached to the ConnectionString property of a File Connection Manager:

@.[System::PackageName] + (DT_STR, 4, 1252) DATEPART( "yyyy", @.[System::StartTime] ) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "mm", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "dd", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "hh", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "mi", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "ss", @.[System::StartTime] ), 2) + ".log"

Which looks horrible, I know, but evaluates to something alot more user-friendly:

PackageName20060103084130.log

You can choose to make this as difficult or simple as you like. For example

@.[System::PackageName] + (DT_STR, 30, 1252) @.[System::StartTime]

evaluates to:

PackageName03/01/2006 08:41:30

Hope that helps!!

-Jamie

|||Thanks a lot! this gives me a real good start to working more with expressions in SSIS|||

one more question. I tried putting it at the end of my string but it doesn't like it because it is malformed:

C:\Documents and Settings\sss\Desktop\output.txt + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()

|||

Gotta escape the '\' character and gotta be in quotes... :)

"C:\\Documents and Settings\\sss\\Desktop\\output.txt" + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()