I've got a CLR i created that does a proof of concept reading of mail from a POP3 server; i'm slapping together an article for SSC on it;
The problem is, it's the carpenter and the nail syndrome; I have a hammer(TSQL), and then every problem looks like a nail....
TSQL might not be the best place to implement this, especially if the email has one or more attachments, and you need to save them so they can be processed by your SSIS package.
(i have not got my CLR to download & Save Attachments yet)
my mail server, for example, might take 10 seconds to a couple of minutes to finally answer a request for getting the list of mail; that kind of waiting doesn't belong in a TSQL process.
an .net app or something that reads the mailbox every x minutes , saves attachments to disk sounds like something that should be done outside of TSQL, even if you can bend a solution to your will.
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!