How Can I Update Data From An Email Attachment ?

Question

How Can I Update Data From An Email Attachment ?

clucasi

SSCrazy

Points: 2113
More actions
June 11, 2012 at 6:24 am

#262257

I need to update SQL 2005 from an email attachment, which is a delimited Excel 2010 file. The same named file is delivered to me with updated data throughout the day. Does anyone know, how I can automate this to update SQL 2005

Viewing 7 posts - 1 through 6 (of 6 total)

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

Andrew G SSChampion Points: 12809 More actions · Answer 1

There's no simple way to automate the retrieval of the email attachment in SSIS specifically. You could write a custom PowerShell script and execute that as a custom task or script within SSIS. Then import the data like any other file.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

If your company is willing to spend some money, you may want to look at this: http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/SSIS-read-email-source.aspx.

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

clucasi (6/11/2012)
I need to update SQL 2005 from an email attachment, which is a delimited Excel 2010 file. The same named file is delivered to me with updated data throughout the day. Does anyone know, how I can automate this to update SQL 2005

If you can use third-party solutions, check the commercial COZYROC Receive Mail Task. You can retrieve email from your server using IMAP/Exchange/POP3 protocol , then extract the attachments from the email. No programming skills are required.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

clucasi (6/11/2012)
I need to update SQL 2005 from an email attachment, which is a delimited Excel 2010 file. The same named file is delivered to me with updated data throughout the day. Does anyone know, how I can automate this to update SQL 2005

Did you ever get this resolved?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

clucasi SSCrazy Points: 2113 More actions · Answer 5

No, the work around was to write script that extracts the attachment and saves the file to a folder, its a bit clunky and doesn't always work, I have looked at the third party solution but my company doesn't want to use them.:-P

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

clucasi (7/2/2012)
No, the work around was to write script that extracts the attachment and saves the file to a folder, its a bit clunky and doesn't always work, I have looked at the third party solution but my company doesn't want to use them.:-P

There are lots of people on this forum that are fluent in C#, myself included. If you post your script and describe some of the conditions that occur when it 'doesn't always work' you may get some assistance shoring it up, so it is a bit more reliable.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato