January 24, 2011 at 3:30 pm
I have a filename of the format
T2011012412ABC
Where 2011= year
01=Month
24=Date
12=hour
I am using a flat file and the data,month,time changes for each file. I am getting the results for yearMonthDate correct. But need help in the time part (00 to 23)
@[User::DataLoadDir] + "T" + (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + (LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) + (LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +"ABC" +".log"
Thanks,
PSB
January 25, 2011 at 12:27 am
What are you trying to do?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 25, 2011 at 7:44 am
I have to make the filename for the flat file dynamic of the given format. I will have it every hour. Is there a better idea to do that ?
January 25, 2011 at 10:37 am
It's an ungainly expression, but as you've nearly got it completed, I guess there's no good reason to try something else.
Have you tried using
DATEPART("hh",GETDATE())
?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 25, 2011 at 10:58 am
Just a quick addon to Phil's suggestion which is inline with what you're attempting, a quick and dirty alternative:
Use this in an execute SQL task and feed the result set to a local variable in the package, then just throw the variable around in the expressions:
SELECT LEFT( REPLACE( REPLACE( REPLACE( convert( varchar(30), getdate(), 121), '-', ''), ' ', ''), ':', ''), 10)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2011 at 11:51 am
Thanks. That worked !
Is there any other way to make the flat file name dynamic ? That is not do any of these and just pick up any file from a particular folder . I am using a foreach loop container and set delay valdation of the flat file to True. Also in the expression builder for the connection string property using the following @[User::TW_File_Name] .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply