Download Emails & Email Attachments in SSIS

Ever need to download emails and attachments using SSIS? If so you have come to right the place. Task Factory, a suite of custom SSIS components by Pragmatic Works has a component called the Email Source. This feature allows you to pull content directly from an email into a data flow.The component supports both POP3 and IMAP and is really easy to set up, so let’s get started!

There is also full support for filtering messages based on sender, message to, subject, date received, body and priority.This way you can filter down to only the messages you want to load.

If you are one of the poor unfortunate souls that don’t yet have Task Factory you can give it a try with a free trial download here: http://pragmaticworks.com/Products/Task-Factory

Walkthrough:

1. Create a new package and bring in a data flow task.
2. Inside the Data Flow Task pull in the TF Email Source component.
3. Open the TF Email Source. Once open we need to create our connection manager. You can choose between POP3 or IMAP for reading emails. I prefer IMAP because it allows you to choose specifically what folder in your email you want to read from. More on this later.
4. On the general tab of the connection manager fill in the following items:
   1. Protocol Type
   2. Mail Server
   3. User Name
   4. Password
      
5. On the advanced tab of the connection manager fill in the following items:
   1. Server Port
   2. Encrypted Connection
   3. Root Folder Path (Directory to read from)
6. Click ok to close out the connection manager.
7. Next select the location to save attachments. “Attachments Directory”
8. I created a variable to store the message ids in. I won’t be using this object variable in this blog.
9. Finally if we want to add a filter we can do that below as well.
10. Now simply connect the email source to a destination component.

Follow Mitchell on Twitter at https://twitter.com/MitchellSQL or his blog at MitchellSQL.wordpress.com.

Have more SSIS questions for Mitchell? Please post them in the comments section below and he will be happy to answer them!