Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Month "Name" from Date in SSIS Expression

Status
Not open for further replies.

LearningSql

Programmer
Apr 16, 2002
48
US
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.
 
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.
 
Thank You. I will try the code and let you know if I was able to get it to work. Much obliged!
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top