Need expression of file name attachment to include the Month in the Year in a specific format.

Question

Need expression of file name attachment to include the Month in the Year in a specific format.

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 30, 2014 at 7:40 am

#308679

I need expression of file name attachment to include the Month in the Year in a specific format.
This is the current expression:
@[User::v_FileAttachmentPath] + @[User::v_FileNameAttachment] + "_" + (DT_WSTR, 2)MONTH( GETDATE() ) + "_" + (DT_WSTR, 4)YEAR( GETDATE() ) + ".xlsx"
It returns the following:
\\ch-file-02\ISData\\New\Projects\24856_Identity_Theft_Reporting\IDTheftReport.xls_6_2014.xlsx
I need the name of the Excel File name to be IDTheftReport_May_2014.xlsx.
How could I do that? In a script task or an expression?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

The number for the month is ok but I need to to list the prior month.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Actually the name of the file should be dynamic in the OLE DB Excel Connection and that name to subsequently be used with the Send Mail Task as an attachment.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Maninder Kumar SSChasing Mays Points: 606 More actions · Answer 3

Hi Welsh,

Do you use Foreach loop container to traverse through these file(s)? I had faced similar situation, where I stored the Fully Qualified file name of file in a user variable and then used the String functions (Substring, Findstring) expressions in derived column to retrive the required files and subfolder names.

Cheers!

Maninder

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Maninder Kumar (7/11/2014)
Hi Welsh,
Do you use Foreach loop container to traverse through these file(s)? I had faced similar situation, where I stored the Fully Qualified file name of file in a user variable and then used the String functions (Substring, Findstring) expressions in derived column to retrive the required files and subfolder names.
Cheers!
Maninder

Yes I have done that before. But in this case I'm assigning the name a template Excel File to the current date. I have some code that does that but I not only need to assign it a file name I need to attach that file to an FTP Task. I had to put that aside for now.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/