Sending an EMail after Map Success with SQL Table snippet
Matthew asked 6 years ago
I am quite new to using SmartConnect and was looking for help in sending an Email after my map has succeeded that contains a few columns from an SQL table. Could anyone help me achieve this?
Thanks!
Patrick Roth Staff answered 6 years ago
You could use a SQL Task and set the globals that way- but you can only set one field per SQL Task but would work.
Or if you have the data during map processing, you could set the globals there as well (document task or calculation, etc)
Then in your Email Task – put the global variables in the email and they will be replaced with their correct values when the email is sent.
Matthew Falle replied 6 years ago Hi, thanks for your reply. I have tried to create a global variable with an SQL Command selecting a view (for reference http://imgur.com/a/rLQS3). After this the an email is sent that contains the variable which should be my SQL view? Could you point out where I am going wrong? Thanks.
Patrick Roth Staff replied 6 years ago The Global Variable being sent from the SQL Task only will hold _one_ value, not a resultset.
so more like:
select top 1 Column1Name from MyTable
So if you are trying to return a resultset like this and attach it into the email body – the SQL Task approach isn’t going to work at all.
So a couple of ways that I can see this working:
1. Create a post map script task with c#/vb
you can use the script template if you want
in it, make a SQL connection to your data and run your query as you are to get the resultset
now in your code, loop through the resultset & data and format a global variable with your data columns and adding the return at the end.
do that for the entireresultset – it’ll all go into a Global variable (that you have in the screenshot for example).
Now put that global in the email body and the results should get put into the body. You’d have to make sure you formatted your data nicely when you create the output string.
2. Now you might not want the result in the Body as above, you want it as an attachment. SC won’t let us add an attachment to the email that we generated (since there isn’t a spot for it in the UI currently).
So what you could do is make your query as #1. But write the output to a text file (or run a second map which does the select * and write _that_ output to a text file).
In either case, you end up with a text file sitting on the drive somewhere.
Next, make another script task. In this one, you’ll need to write code to send an email using whatever method that you like.
you would compose the email address yourself (from/to/credentials etc).
now since you are creating the email yourself, you can add whatever attachments that you like and then send it.
this would be a good amount of work i’d think but there will be example of sending email on the internet
Matthew Falle replied 6 years ago Creating and sending a file as an attachment is the avenue I have been exploring, Using a script I have tried to send the email with an attachment however I am getting the error “The remote certificate is invalid according to the validation procedure.”. I think this is a security issue preventing the email from sending.
I could also do this by attaching a view, but I am not sure how to work this in.