• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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