SSIS on SQL 2008R2: Can't get Send Mail Task to work with run-time configuration
-
I have a simple package (just a Send Mail Task) for testing.
I configure the task with test values for To,From, Subject,Body and Attachment (points to a real file, though!)
I set up an SMTP connection manager, call it SMTP (not too original, I know!) and put in a default SMTP server.
Next, I set up a package configuration for the SMTP connection, specifying the connection string. The configuration is stored in a table (SSIS_Configurations) in a database we use for just this purpose. (All our databases have connection strings stored there as well for run-time configuration. Works well!)
So, on the config table, the row looks like:
ConfigurationFilter: Connect_SMTP
ConfiguredValue: SmtpServer=xxxxxxxx;UseWindowsAuthentication=True;EnableSsl=False; PackagePath: \Package.Connections[SMTP].Properties[ConnectionString]
ConfiguredValueType: String
When I run this however, I get messages like this:
Error: 2015-08-26 11:29:04.66 Code: 0xC00291C4 Source: Send Mail Task Send Mail Task Description: The Simple Mail Transfer Protocol (SMTP) server is not specified. Provide a valid name or IP address of the SMTP server. End Error
Error: 2015-08-26 11:29:04.66 Code: 0xC00291C3 Source: Send Mail Task Send Mail Task Description: No recipient is specified. End Error
Error: 2015-08-26 11:29:04.66 Code: 0xC0024107 Source: Send Mail Task Description: There were errors during task validation. End Error
Error: 2015-08-26 11:29:04.66 Code: 0xC00291D7 Source: Execute SQL Task Execute SQL Task Description: No connection manager is specified. End Error
Error: 2015-08-26 11:29:04.66 Code: 0xC0024107 Source: Execute SQL Task Description: There were errors during task validation. End Error
Error: 2015-08-26 11:29:04.66 Code: 0xC002F304 Source: Send Mail Task Send Mail Task Description: An error occurred with the following error message: "Failure sending mail. System.IO.IOException: Unable to read data from the transport connection: net_io_connectionclosed.". End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:28:43 AM Finished: 11:29:04 AM Elapsed: 21.528 seconds. The package execution failed. The step failed.
Which seems totally bogus, since I specified the connection string in the package configuration.
Thinking I had messed up the package configuration, I added a script task to verify:
public void Main()
{
var myConn = (Dts.Connections["SMTP"]) as Microsoft.SqlServer.Dts.Runtime.ConnectionManager;
var fireAgain = true;
if (myConn != null)
Dts.Events.FireWarning(1, "smtpconn", myConn.ConnectionString, string.Empty, 0);
else
Dts.Events.FireWarning(1, "smtpconn", "connection not found", string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Success;
}
Looking in the log after the job fails, I can see the messages from the script task, and the connection string is indeed configured.
Any ideas what I might be missing? This should be easy, but...
Gerald Britton, Pluralsight courses
-
If you remove the configurations and just hardcode everything, does it work then?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Yes it does. But I need the flexibility to configure dynamically since I deploy to multiple domains
Gerald Britton, Pluralsight courses
-
g.britton (8/27/2015)
Yes it does. But I need the flexibility to configure dynamically since I deploy to multiple domainsOkay, so we know it's not the tasks themselves that fail, but rather the configuration.
What if you do not configure the connection string, but rather the individual properties of the SMTP connection manager?
(for example only the Server Name)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (8/27/2015)
g.britton (8/27/2015)
Yes it does. But I need the flexibility to configure dynamically since I deploy to multiple domainsOkay, so we know it's not the tasks themselves that fail, but rather the configuration.
What if you do not configure the connection string, but rather the individual properties of the SMTP connection manager?
(for example only the Server Name)
Yup, I tried that. I even set up a more elaborate test:
1. Script task enhanced to show variables I use in the expressions for the Send Mail Task
2. Logging enabled to get the script results in the log.
Send Mail Task:
Script:
public void Main()
{
// Variables
if (Dts.Variables.Contains("EmailTo"))
Dts.Events.FireWarning(1, "SMT", "EmailTo: " + Dts.Variables["EmailTo"].Value.ToString(), String.Empty, 0);
if (Dts.Variables.Contains("EmailFrom"))
Dts.Events.FireWarning(1, "SMT", "EmailFrom: " + Dts.Variables["EmailFrom"].Value.ToString(), String.Empty, 0);
if (Dts.Variables.Contains("EmailSubject"))
Dts.Events.FireWarning(1, "SMT", "EmailSubject: " + Dts.Variables["EmailSubject"].Value.ToString(), String.Empty, 0);
if (Dts.Variables.Contains("EmailBody"))
Dts.Events.FireWarning(1, "SMT", "EmailBody: " + Dts.Variables["EmailBody"].Value.ToString(), String.Empty, 0);
// Connection information
var myConn = (Dts.Connections["SMTP"]) as Microsoft.SqlServer.Dts.Runtime.ConnectionManager;
//var fireAgain = true;
if (myConn != null)
{
//MessageBox.Show("SMTP Connection String: " + myConn.ConnectionString);
Dts.Events.FireWarning(1, "SMT", "ConnectionString: " + myConn.ConnectionString, string.Empty, 0); //, ref fireAgain);
}
else
{
//MessageBox.Show("SMTP Connection not fount!");
Dts.Events.FireWarning(1, "SMT", "connection not found", string.Empty, 0); //, ref fireAgain);
}
// Look for SMTP Server.
foreach (var conn in Dts.Connections)
{
//MessageBox.Show(conn.Name);
var connProperties = conn.Properties;
if (connProperties.Contains("SmtpServer"))
{
var connProp = connProperties["SmtpServer"];
var propVal = connProp.GetValue(conn).ToString();
Dts.Events.FireWarning(1, "SMT", "SmtpServer: " + propVal, string.Empty, 0); //, ref fireAgain);
// MessageBox.Show("SmtpServer: " + propVal);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
Log results:
starttime Event datacodemessage
2015-08-27 09:35:24.000PackageStartTestSMTPBeginning of package execution.
2015-08-27 09:35:24.000DiagnosticTestSMTPBased on the system configuration, the maximum concurrent executables are set to 6.
2015-08-27 09:35:24.000OnWarningTestSMTPEmailTo: gerald.britton@td.com
2015-08-27 09:35:24.000OnWarningTestSMTPEmailFrom: gerald.britton@td.com
2015-08-27 09:35:24.000OnWarningTestSMTPEmailSubject: test subject
2015-08-27 09:35:24.000OnWarningTestSMTPEmailBody: test body
2015-08-27 09:35:24.000OnWarningTestSMTPConnectionString: SmtpServer=CRWBDVCP01CRU.D2-TDBFG.COM;UseWindowsAuthentication=True;EnableSsl=False;
2015-08-27 09:35:24.000OnWarningTestSMTPSmtpServer: CRWBDVCP01CRU.D2-TDBFG.COM
2015-08-27 09:35:45.000OnError TestSMTPNo connection manager is specified.
2015-08-27 09:35:45.000OnError TestSMTPThe Simple Mail Transfer Protocol (SMTP) server is not specified. Provide a valid name or IP address of the SMTP server.
2015-08-27 09:35:45.000OnError TestSMTPThere were errors during task validation.
2015-08-27 09:35:45.000OnError TestSMTPNo recipient is specified.
2015-08-27 09:35:45.000OnWarningTestSMTPSSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
2015-08-27 09:35:45.000OnWarningTestSMTPThe address in the "From" line is malformed. It is either missing the "@" symbol or is not valid.
2015-08-27 09:35:45.000OnWarningTestSMTPThe subject is empty.
2015-08-27 09:35:45.000OnError TestSMTPThere were errors during task validation.
2015-08-27 09:35:45.000OnError TestSMTPAn error occurred with the following error message: "Failure sending mail. System.IO.IOException: Unable to read data from the transport connection: net_io_connectionclosed.".
2015-08-27 09:35:45.000OnWarningTestSMTPSSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
2015-08-27 09:35:45.000PackageEndTestSMTPEnd of package execution.
Gerald Britton, Pluralsight courses
-
So if you fill in actual values for those variables (and no configuration), the package works flawlessly, but once you turn on configurations the package fails?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (8/27/2015)
So if you fill in actual values for those variables (and no configuration), the package works flawlessly, but once you turn on configurations the package fails?yup!
Gerald Britton, Pluralsight courses
-
Found the problem: As simple as a typo in the servername!
Fixed the typo, all works as designed. I can configure the SmtpServer, the ConnectionString or both.
Pity that the error messages led me down a completely different rabbit hole.
Thanks for your input and patience!
Gerald Britton, Pluralsight courses
-
g.britton (8/27/2015)
Found the problem: As simple as a typo in the servername!Fixed the typo, all works as designed. I can configure the SmtpServer, the ConnectionString or both.
Pity that the error messages led me down a completely different rabbit hole.
Thanks for your input and patience!
Ah, typos in servernames are impossible for other people to spot 😀
Glad you got it resolved 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply