Send Mail Task

Question

Send Mail Task

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 22, 2011 at 2:24 pm

#262155

Hi SSIS Gurus,
I have a SSIS Package that truncates and loads a SQL Server Table, Exports the Table to an Access Database and appends the Date as a suffix YYYY-MM-DD to the end of the Access Database.
I assume that I can't use the Send Mail Task if the File Names is dynamically generated?
Can I do this and if not what are my options?
If anyone has some sample code that would be awesome.
Happy Holidays,
Welsh:-)
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 8 posts - 1 through 8 (of 8 total)

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

Indeed you can, use an expression to change I believe the FileAttachments property to the location of the file you want to attach. Here's some code that formats a date as YYYYMMDD.

"C:\\test" + (DT_WSTR, 10) YEAR( GETDATE() ) + (MONTH( GETDATE() ) < 10? "0" + (DT_WSTR, 10) MONTH( GETDATE() ):(DT_WSTR, 10) MONTH( GETDATE() )) + (DAY( GETDATE() ) < 0? "0" + (DT_WSTR, 10) DAY( GETDATE() ):(DT_WSTR, 10) DAY( GETDATE() )) + ".csv"

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

Thanks @@ZZartin. 🙂

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 3

When I create the file I use an Expression in the Connection String Property.

How do I save the expression to a variable when creating the file and use that variable in the Connection String Property of the EMail Attachment?

Thanks for your help!

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/

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 4

Yep I believe you can create a variable in the package and set EvaluateAsExpression to True and set the expression for that variable to the file path, then use that variable in the expressions you use to set the other properties.

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

ZZartin (12/23/2011)
Yep I believe you can create a variable in the package and set EvaluateAsExpression to True and set the expression for that variable to the file path, then use that variable in the expressions you use to set the other properties.

Could you please expand on that?:unsure:

It would be awesome if you could provide a step by step explanation.

Thanks again.:-)

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/

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 6

In the control flow right click it and open the variables screen, create a new variable and set the type to string. When you have that variable selected the properties screen should show the properties for that variable, that's where you can set the EvaluateAsExpression property to true and the Expression to your file path.

Hmm... i did notice a couple issues with this though when I was trying it out. For one thing the variable reevaluates each time it's used so the date could potentially change and the package validation fails since the send mail task can't find file since it hasn't been created yet when using the variable.

You could potentially use a script task to set the variable at the beginning of the package but it might be easier to just use the same expression in the connection string and the attachment property.

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

Thanks for the feedback.:-)

When I first used the script task in my first Package to assign a value to a variable I thought that the code was not updating the variable but it turned out that unlike DTS the change to the variable is not persistent.

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/