Email Attachments - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Email Attachments

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-02-18 : 06:29:18

Hi

Each month I receive an email, with an xml file attachment which has to be imported into a SQL database.

Is it possible using SSIS to extract the attachment in the email as a process?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 07:29:37

Not sure you can extract xml from attachment.Use XML source task for source connection and OLEDB/SQL Server destination connection in SSIS. Alternatively you can also use BULK INSERT task to import it to table.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-02-18 : 07:54:13

Hi

Is there a way of downloading the attachment through SSIS?

Thanks

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 10:45:24

There is no native way in SSIS to download the attachment from an email server. Depending on your server, you can most likely set up a rule on the server to save attachments to a certain location when the emails come in. If you don't have this ability with your server, you could always create your own ControlFlow Task component to connect to the server and retrieve the attachment. I could help guide you through that process if you need the help and can give me sufficient details.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-02-18 : 10:57:18

Hi

Thanks for that Qualis, what details would you need?

I was also looking into running that part as VB Script which the SSIS package would run.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 11:52:59

You could use a script. What email server do you have? Does it have an API you can use to access email?

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 11:59:47

If you are using Exchange, the following might help:
http://www.codeproject.com/KB/vb/AccRemoteExchange.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-02-18 : 13:35:00

Hi

Thanks for that, i will give it a try.

Also how do you create a custom task?

Thanks

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 14:00:42

Start here:
http://msdn2.microsoft.com/en-us/library/ms345156.aspx

I know it is pretty lame and alot is not explained, but I haven't really found another good guide. I am planning on writing one soonish, but I haven't had time yet.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-02-19 : 04:00:12

Thanks, I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources