August 10, 2021 at 7:10 pm
I have a package deployed to the Integration Services Catalogs and it's working well. In Visual Studio I edited my package to use a Script Task to produce an email notification. This runs fine in debug but when I re-deploy my package, everything works well still except the email script is not running when the package is called using SQL Agent. Any clues? Just for the record here is the C# that is working in debug:
using System.Net;
public void Main()
{
try
{
MailMessage mailMsg = new MailMessage();
mailMsg.To.Add(new MailAddress("myname@company.com", "Support"));
mailMsg.From = new MailAddress("matrix2@company.com", "ETL Process");
mailMsg.Subject = "SUCCESS: Users File Loaded";
string text = string.Concat("The ETL Process successfully loaded all the Users File data table.");
SmtpClient smtpClient = new SmtpClient("client.server.net");
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential("matrix2@company.com", "mypassword");
smtpClient.Credentials = credentials;
smtpClient.Port = 25;
smtpClient.EnableSsl = true;
smtpClient.Send(mailMsg);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
August 10, 2021 at 7:21 pm
Not sure I can help, but first thing I'd do is change the C# you posted to exclude the username and password and smtp client name. don't need people from the forum (or random strangers on the internet) logging into the server and sending mail out.
Next, I'd check the logs.
Next, I'd try changing the console.writeline to write to a file instead of console. That way you can see the error that came up.
If I had to guess, I would say the error is related to the server hosting SSIS not having the SSL certificate trusted, but that is just a random guess as there are no logs to review.
My GUESS is that the script is running, but the TRY is hitting an error, it is going to the CATCH block, writing the error to nowhere then reporting success as you are setting the task result to success. You MAY want the task to fail on error not succeed, but maybe not.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 11, 2021 at 3:26 pm
Thanks... How do I write to a file in C#? I'm really new to C#
August 11, 2021 at 3:55 pm
Something along the lines of:
string text = ex.Message;
string path = @"C:\Data\TextFiles\logFile.txt";
System.IO.File.AppendAllText(path, text);
Although, if you are "really new to C#", I would recommend reading up a bit on C# coding prior to putting it into an automated process like this. The reason being - support. If you can't support the code, do you really want it in a production system?
Now, I am not saying that having to google or look up help online on forums such as this one is a bad thing. I just know my skillset is in SQL Server stuff and less in C#, so when I design things like this, I tend to stick more on the SQL side and less on the C# side. I do know some C# (enough to get by, and google or read the docs for what I don't know), but I don't know a good way to debug C# code in SSIS. In visual studio with a C# program it is EASY. In SSIS, I'm not sure how you start. So for sending mail, I would use the built-in SQL Database Mail. I know how that works, I know how to test that, and I know how to test running it with different credentials and can verify that it is working. With C# script inside SSIS, it could be many things that are wrong. It could be missing certificate, could be missing .NET version, could be firewall, could be something I am not thinking of. Plus, I HATE storing credentials in a plain-text file like that, especially when dealing with version control and password policies. Toss it into git and now everyone who has permissions to that project can grab it and send mail as that user. If I set it up with Database Mail, the password is protected inside the database along with the connection information. That last bit is helpful if your IT department ever decides to change mail servers. If it is hard-coded into your SSIS package, then you are needing to redeploy the SSIS package with the new credentials and configuration every time it changes. If it is using Database Mail, you don't have to worry about the credentials or server configuration changing at the SSIS package level - just at the SQL instance level.
Just my 2 cents though. If that C# script is doing more than just sending email, then it may be better to leave it in C#.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 12, 2021 at 1:34 pm
I too think I feel more comfortable keeping email notification in SQL Server rather than SSIS as I've learned that it is very versatile. The below code actually works for another email task I'm working on. Should I wrap this up in a stored procedure and call it as a step in SQL Agent's job? Also is there a way of just calling the step if the job is successful?:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'Today''s SMS Count
' +
N'<table border="1">' +
N'<th>Today''s SMS Count to Date: </th></tr>' +
CAST ((SELECT COUNT (*) FROM [mydatabase].[dbo].[mytable]
with (NOLOCK)) AS NVARCHAR(MAX)) + N'</table>';
EXEC msdb.dbo.sp_send_dbmail @recipients='camp@company.com;',
@subject = 'Today''s SMS Count - HTML Formatted',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'SQLMail';
August 12, 2021 at 1:58 pm
Dumb question... I can just run the email step as a result of a successful run of the first step.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply