Execute SQL and place results into email

Question

Execute SQL and place results into email

fec

SSC Eights!

Points: 941
More actions
April 20, 2008 at 11:37 pm

#128626

Firstly I am new to Integration Services...so be gentle.
I need to execute an sql 2005 stored proc and send an email for each record in the recordset.
Any help or pointers is greatly appreciated.
p

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You may want to give a better description of what you are trying to do. This is not necessarily what SSIS is made to do.

It can, however, do this in a couple of ways.

The first is with a loop container

1) Add a SQL Command component to your control flow

2) Add a ForEach container to your control flow

3) Add a Sendmail task to the ForEach loop container

The tricky part is you need to execute the SQL to get your list in the SQL Command and return the FullResultSet to a variable of type object. You can then use this in the ForEach loop with the ADO Recordset enumerator. You can then set variables based on the columns within the recordset and use them in the Sendmail task. Remember that a string variable can be a maximum of 4000 characters.

The second option is with a script task.

This is done somewhat the same way. Set an object variable to the result set of the SQL command. Then, loop through the recordset in the script task (you have to write some VB.Net for this) and send the emails. You may need to go to this option if you need a complicated email message body or you have some text more than 4000 characters.

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

Michaels answer is dead on. You should probably lean towards the script task as it will provide better control over the message and/or attachments you may need in the future.

Also, reuse of the script could help you out on other email type of issues.

Marvin Dillard
Senior Consultant
Claraview Inc

fec SSC Eights! Points: 941 More actions · Answer 3

Thanks Michael,

without asking you to write the solution for me (although feel free 🙂 ) , can you point me in the direction of some example code which would be helpful in writing the scripting option as i have no idea.

regards,

Paul

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 4

Hi Paul,

Here are 2 scripts that do the same thing... One uses the .net 1.0 system.web.mail class and the other the .net 2.0 system.net class.

Hope this helps...

Kindest Regards,

Frank Bazan