[SOLVED] SSIS dynamic SMTP mail server name | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

[SOLVED] SSIS dynamic SMTP mail server name

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 correct
b> 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? Panic, Chaos, Disorder … my work here is done –unknown 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).
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? Panic, Chaos, Disorder … my work here is done –unknown
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::FromAccount
User::Header
User::Body
User::MailServer <– possibly unnecessary now – may be removed
User::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::FromAccount
SUBJECT == User::Header
MESSAGESOURCE == User::Body
TOLINE == 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::MailServerConnectionString At last!! Panic, Chaos, Disorder … my work here is done –unknown
hi, altough it has been long time from the post, can you explain how you did the last part
"ConnectionString == User::MailServerConnectionString"?
]]>