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?
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())
?
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy