I created a package to extract data from the database, put the data in an Excel file, zip it, and then email the file. I ran into a roadblock when my stmp server required authentication on a secure port (not port 25).
I found a work around to send email in an SSIS package if your smtp server requires authentication. You can also send an HTML email.
You must have Database Mail already set up with the proper authentication. If you don’t, there are many articles that show you how to set up database mail. I will tell you that if you are using port 465 on your stmp server, for some reason, you need to specify port 587 in database mail.
In your SSIS package, simply add an “Execute SQL Task” step to your package.
In the SQLStatement entry box under the General Tab type:
@profile_name = 'Your Database Mail Profile Name’,
@subject = 'Your Subject',
@body = 'your body,
Database mail only allows a 1MB attachment as a default but this can be changed in “View or change system parameters”, “Maximum File Size (Bytes)”.
I hope this helps.