Post reply

Import data from email attachments

  • January 6, 2008 at 9:31 pm

    #119920

    I have staff manually trawling through email folders in Outlook, saving the attachments and then loading the data from those files into a SQL Server database. Is it possible to write a script to automate this process and do it in an SSIS package?

    I am not sure how to go about accessing and saving the email attachments. We are currently using Microsoft Exchange Server 2003 and SQL Server 2000, although we will be changing to SQL 2005 very soon.

    I have been looking around for any info on this but don't see any, other than a book by Thomas Rizzo called "Programming Microsoft Outlook and Microsoft Exchange 2003", which I do not have but leads me to believe it might be possible to do what I want. Can anyone point me to any other resources on this topic?

  • January 7, 2008 at 1:41 am

    #765255

    Well, I'm not a programmer first and foremost, so I can't give you a definitive answer, but I can see no reason why this isn't possible. However, you might also want to think about doing it the other way round.

    Let me explain. We have a helpdesk application based on a SQL db that can store files as part of each call. Often, people want to send informational files (attachments, screenshots etc.) by email, and we didn't want the hassle of wading through them to upload the attachments manually, so we used the Eventsink (take a look at http://msdn2.microsoft.com/en-us/library/aa486233.aspx for further details) capabilities of Exchange. This now means that whenever an email lands in a particular mailbox, the eventsink process will be triggered and will run a bit of vbscript configured to do whatever we want - in this case, iterate through the email to find inline screenshots and/or attachments, and to upload them to our SQL db.

    Sounds pretty close to what you want to achieve.

    Semper in excretia, suus solum profundum variat

  • January 7, 2008 at 1:58 pm

    #765509

    Thanks. I will look into the event sink capabilities. It does sound like what I am after.

  • June 25, 2008 at 12:15 pm

    #834197

    Did you figure out how SSIC can be use to import email attachment data ? I get email everyday with xls attachment which needs to be imported into SQL table.

    Thanks

  • June 25, 2008 at 2:47 pm

    #834300

    Sorry, I've been too busy in the last few months on a project due to go live next week, so I haven't had a chance to look further into it yet. I will have to revisit it in a couple of months time.

  • April 27, 2009 at 3:57 pm

    #984748

    Try this one

    http://www.dbsoftlab.com/Visual-Importer/How-to-load-data-from-an-email-attachment-into-MS-Sql-Server-Database.html

    Mike

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

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