Month "Name" from Date in SSIS Expression

[LearningSql]

In SSIS I am dynamically changing the connection string to point to the correct file to load each night.

Each night, the filename changes to reflect the current day. For example, a file processed tonight (10/22/09) would have the name 22Oct2009.txt

In Connection Manager on my source file, rt-click, properties I can set the Expressions-Connection String to get the day portion and the year portion as numerics however the Month portion always comes out a number. For example this is what shows when I use the statement below 22102009

SUBSTRING( (DT_WSTR,30) GETDATE() , 9, 2)
+ SUBSTRING( (DT_WSTR,30)GETDATE() , 6, 2 )
+ SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 )

I am trying to get the filename to show
22oct2009

Is there a way to have it show oct vs the numeric 10?

I have done a lot of web searching using different terms etc -- no luck so far. Maybe I'm missing something pretty simple...Thanks for your help.

 

[strtech]

Try something like this:

SUBSTRING( (DT_WSTR,30) GETDATE() , 9, 2) +
MONTH(BirthDate) == 1 ? "January" : Month(BirthDate) == 2 ? "February" : "Unknown" + SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 )

Just continue the list for March, April, etc.

 

[LearningSql]

Thank You. I will try the code and let you know if I was able to get it to work. Much obliged!

 

[williey]

the solution i used is to defined the filename as a variable and write a script task to assign the correct filename.

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.