Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2006-09-13 : 06:37:29
|
| Hi All,I'm trying to make my SEND MAIL TASK in SSIS dynamic, by retrieving SMTP Server name (amongst other email message information) from a table.I've got an SP that is getting the information into package variable's, and when I don't try and have a dynamic server name (i.e. I use a specified SMTP server in my SMPT Server connection), then the email get sent off.I'm trying to use an expression to build alter the send mail to use the server name I have in a user variable.I've tried using the expression for the property:SmtpConnection set to: RTRIM( @[User::MailServer]) I also tried without the RTRIM - I only added it because the error messages seem to include a full 'string' length, and there doesn't seem to be a 'varchar' data type for SSIS variables.so:a> Is SMTPConnection the property I need to be setting? None of the others seem correctb> If SMTPConnection IS the property I need, does the value give the SMTP Server Name, or just the Name of the connection from the Connection Manager's?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here!Part-Answer to:b> The SMPTConnection property sets the name of the CONNECTION to be used. That Connection needs to set the name of the server. So n ow, the question becomes, how do I configure that from SQL (I know how to use package configurations from XML files). |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-09-13 : 09:45:15
|
| Progress so far: I am sure I'm being blind here (it wouldn't be the first time - and won't be the last time).I have a package that retrieves some values from SQL table to allow me to dynically create an email message, so it retrieves To Addresses, From Address, Header, Body from a table, populates variables, and uses expressions to assign those to the SEND MAIL TASK.That is working (after a bit of head/wall/banging to get my default virtual SMTP server running locally).Now I am trying to make the SMTP Server Name dynamic. The Property SMTPConnection specifies the SMTP Connection Manager to use, and not the server, so it doesn't seem as though I can configure a property on the SEND MAIL TASK to achieve this.Now I'm focussing on using SQL Package Configurations to try and drive this. As I see it, just like XML configurations, if you change a value in the configuration source, then when the package runs, it get's that value, and uses it.I have change the configuration value (outside of the package). I confirm it is actually changed by running a SQL Task that gets the value from the configuration table, and view the value assigned to the variable in watches. The value returned is a different value (in my case I changed it to an invalid servername), and yet the task still runs through successfully, using the previous SMTP servername.huh?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-09-13 : 10:45:07
|
| After carefully re-reading a section from "Professional SQl Server 2005 Integration Services", I found the solution. I DO want to complain AGAIN about how UN-INTUITIVE some things are is SSIS. Sigh - rant over.OK - the soultion is: On the Connection Manager for the SMTP Connection, right-click and go to properties. Inside that, go to the expressions, and use that to put in the value you have extracted from the DB.So, in my case, I have a SQL task that runs an SP Email.GetEmailDetails , and puts the values returned into 6 variables:User::FromAccountUser::HeaderUser::BodyUser::MailServer <-- possibly unnecessary now - may be removedUser::ToAccounts <-- lesson learnt here - ensure you DO NOT end the list with a ; - you will get a failure complain about a bad address (iirc)User::MailServerConnectionString <--This is the magic that makes it work now.Assign those values to where you need them:4 of them get assigned on the SEND MAIL TASK:FROMLINE == User::FromAccountSUBJECT == User::HeaderMESSAGESOURCE == User::BodyTOLINE == User::ToAccounts but the final expression, as mentioned above, is not done on the send mail, but on the properties on the SMTP Connection:ConnectionString == User::MailServerConnectionStringAt last!!*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
|
|
|
|
|
|
|
[SOLVED] SSIS Dynamic Send Mail Server Name
blog thingie: http://weblogs.sqlteam.com/mladenp