June 7, 2016 at 9:20 am
SSIS is up and running, and I can deploy packages to it. Learned the hard way that I can't run packages built in VS 2013, but that's another issue.
I'm using SSDT 2012 now and I've put a simple send mail task into a package and deployed it to my server. It's a new instance of SSIS, so I'm getting an error when it tries to deliver the message - the SMTP server requires a secure connection or the client was not authenticated
I've tried checking and unchecking the Use Windows Authentication and no dice. It delivers fine when I run it in SSDT, but I don't think I have the account responsible for delivering it from the server configured properly. Can anyone offer any advice? All of my searches yield only how to set up the task or install SSIS, not how to set up the server to do things correctly with permissions etc.
Any advice would be greatly appreciated.
June 7, 2016 at 9:27 am
Does your package involve a SQL Server, and if so, can you send messages from that server with sp_send_dbmail? I always find that easier than using the Send Mail task in SSIS - it's one less moving part.
John
June 7, 2016 at 10:05 am
How are you running the package on the server - through SQL Agent or executing via management studio? Are they deployed into the Integration Catalog (project deployment method) or are they deployed using the legacy package deployment method.
June 7, 2016 at 10:08 am
Yes, I put it on a sql 2012 instance and I'm trying to use the Send Mail task. So you're saying use execute sql task and use the sp?
I was trying to find out which account sends the email to see if I could configure it properly.
June 7, 2016 at 10:15 am
sqlkohai (6/7/2016)
Yes, I put it on a sql 2012 instance and I'm trying to use the Send Mail task. So you're saying use execute sql task and use the sp?I was trying to find out which account sends the email to see if I could configure it properly.
The account that sends the mail depends on how it is being executed. If you are executing it via SSMS then I believe it will use the logged in user. If you are executing via SQL Agent then it will run under the job owner or if the job owner is a sysadmin than it will run under the SQL Agent Service account. you may have to set up a proxy user which has access to the email server and set this as the run as user in the job step.
June 7, 2016 at 10:41 am
I was right clicking on the package and running it, and I was logged into the ssms as me, so it should be able to send it, but it's not sending. I'm going to give your approach a shot.
June 7, 2016 at 10:44 am
Are you using SSMS remotely? Try launching SSMS on the server and running the package from here. If it works then see the url below.
June 7, 2016 at 12:44 pm
Thanks, I'll check out the link when I am finished. Your solution worked perfectly (well, I needed to create a profile to send it with), and I'm cookin with gas. Planning on putting all of this into a blog post.
Thanks SO much for your help.
June 8, 2016 at 10:52 am
quick question - can I feed in a variable with the message in it so I Can include other variables in the message?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply