Readin email using SSIS Script Task

Question

Readin email using SSIS Script Task

quasar_phoenicis

SSCommitted

Points: 1625
More actions
July 22, 2010 at 11:44 am

#141232

hi,
I'm trying to create an SSIS package that reads an email from MS Outlook 2003 (using the subject as the criteria) then save the excel file attachment of that email to a share drive.
Is this doable? Anyone knows how to do this? Pls. help.
thanks!
-poy

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

for whatever reason, reading email and/or attachments was possible in SQL 2000, but deprecated for 2005 and above, i believe.

the code to read a POP3 mailbox in .NET is pretty straight forward; you could create an app/service which does it and sticks the results in SQL server, or even create a web service and have SQL pull the data from a web service.

not sure if you can do it with CLR...it might be a unsafe/permissions thing.

Lowell

--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!

quasar_phoenicis SSCommitted Points: 1625 More actions · Answer 2

thought so.

I tried using tsql thru xp_findnextmsg and xp_readmail. With this, i can get the location of the attachment and have a script copy that file and save to a share drive. I was able to use these 2 stored procedures before with no error but now i'm encountering one:

xp_findnextmsg: Procedure expects parameter @user, which was not supplied.

i don't think these 2 stored procedures requires @users parameter and i can't find any in the website that says so.

anyone knows how to resolve this?

thanks!

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

quasar_phoenicis (7/22/2010)
hi,
I'm trying to create an SSIS package that reads an email from MS Outlook 2003 (using the subject as the criteria) then save the excel file attachment of that email to a share drive.
Is this doable? Anyone knows how to do this? Pls. help.
thanks!
-poy

If you can use third-party solutions, check the commercial CozyRoc Receive Mail Task. You can open MIME-encoded mail file or download directly from POP3 or IMAP email server. The task also supports decryption of S/MIME emails (in case you need that).

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/