• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Expression in SSIS package for file name

Hi,
I need to automate the package such that it will pick the file for yesterday from the below location
X:\Marketing\MetroWeb\MET1D_SERIAL_DAILY_RTN_041311.txt
I am trying to change the given expression :
"/MET1D_SERIAL_DAILY_RTN_"+(DT_WSTR, 8)  ( (DT_I4) ( (DT_WSTR, 4) YEAR( GETDATE()  )  +
             Right("0"+ (DT_WSTR, 2) MONTH( GETDATE() ) ,2) +
             Right("0"+ (DT_WSTR, 2) DAY( GETDATE()  ) ,2) ) - 1 ) + ".txt"
which gives MET1D_SERIAL_DAILY_RTN_20110413
I am trying to modify and it is not working, please help
Thanks
0
sqlcurious
Asked:
sqlcurious
  • 3
1 Solution
 
Jason Yousef, MSSr. BI  DeveloperCommented:
That's easy...

try this :

"\\MET1D_SERIAL_DAILY_RTN_" +

Right( "0" + (DT_WSTR, 2 ) MONTH(Getdate()),2)  +
(DT_WSTR, 8) ((DT_I4)Right( "0" + (DT_WSTR, 2 ) DAY (Getdate()),2)    -1) +

Right((DT_WSTR,4)YEAR(GETDATE()),2)   + ".txt"



0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
I've it in my blog by the way, please consider following :)

http://asqlb.blogspot.com/2010/11/ssis-get-date-with-leading-zeros.html
0
 
Alpesh PatelAssistant ConsultantCommented:
"\\\\" +  @[User::FILE_SHARE] + TRIM(@[User::Done]) + LTRIM(@[User::FileName]) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)  + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) +  RIGHT((DT_WSTR,4)YEAR(GETDATE()),2) +   ".txt"
0
 
sqlcuriousAuthor Commented:
thnks!
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Thanks for the grade and the points :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now