send mail task with dynamic attachment name

Question

 

I saw couple of posts about this, but solutions there didn't help me at all.

 

In my package I have a package scope variable that stores the path name. Which is set to following path in a script task.

 

"d:\\logs\\ssis\\net10\\"

 

My excel connection uses this user variable to write data to an excel file. I use the following expression to create the file, and to be able to achieve that I set delayvalidation property to true.

 

@[User::excel_file] + "Region_10_" + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"

 

Up to this point everything works great, data flow runs without any problems and the file is correctly created on the server.

 

At the end of my package I decided to add a send mail task and the problem starts here.

 

I use the same expression above in the attachment field and yes I did set delayvalidation property to true for send mail task.

 

@[User::excel_file] + "Region_10_" + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"

 

But I get the following error every time I ran the package.

 

Send Mail Task, Send Mail Task: Either the file "@[User::excel_file] + "Region_10_" + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"" does not exist or you do not have permissions to access the file.

 

Instead of having that dynamic attachment name if I hardcode the path for the file that will be created when the package will be run, everything runs without a problem. So I am guessing there's not a problem about permissions, somehow that expression is not being interpretted correctly in attachment field, that's why error has the expression instead of its run time value.

 

Can somebody please point out what I am doing wrong here?

 

Erinc

 

 

Answer 1

When you say you set it in the attachment field I guess you mean you typed (or pasted) or pasted it into the attachment field on the Mail page.  The problem is that that is not where you set expressions.  In order to do what you want you need to go to the Expressions page and add an expression for the FileAttachment property to this expression instead.

 

Matt

 

Answer 2

 

Thanks Matt;

 

That worked. I realized it was a really simple thing what I struggled for the last hour.

 

Erinc