SSIS - Read email attachment - 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)

SSIS - Read email attachment

Author

Topic

DesiGuju
Starting Member

12 Posts

Posted - 2008-06-20 : 15:20:25

Good Afternoon,

Everyday I receive an email with an Excel file attachment. Which has to be imported into a SQL database. Can I use SSIS to download the file and import into SQL table?

Thanks In Advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-20 : 15:26:00

What email program do you use? Is the attachment accessible on your hard drive? Is SQL Server on your desktop or on a server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DesiGuju
Starting Member

12 Posts

Posted - 2008-06-20 : 15:28:17

Thanks for the quick reply.

1-Email - Outlook
2-Attachment can be accessible from local hard drive
3-SQL Server is on a server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-20 : 15:39:12

Yes SSIS can do this. If you want to schedule the package to run daily, you'll need to make the file accessible via a share and provide permissions to the SQL Server service account.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DesiGuju
Starting Member

12 Posts

Posted - 2008-06-20 : 15:41:15

I have not use SSIS before. Can you walk me thru the step or refer me to website where i can get the information ?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-20 : 15:44:17

Check out SQLIS.com

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DesiGuju
Starting Member

12 Posts

Posted - 2008-06-20 : 15:46:10

That link doesn't work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-20 : 16:07:40

quote:
Originally posted by DesiGuju

That link doesn't work

I updated my post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DesiGuju
Starting Member

12 Posts

Posted - 2008-06-20 : 16:39:10

Thank You

ntantia
Starting Member

1 Post

Posted - 2009-01-15 : 01:10:21

I am trying to achieve the same - read attachment from an outlook mailbox and push the data into database.

I tried to write a custom email source adapter but it fails somewhere..am new to ssis..not sure if I should be writing one custom source or it could be done without it.

I would be glad if you could give me the steps for the same.

Thanks in advance.

BellaScout
Starting Member

25 Posts

Posted - 2009-02-10 : 18:15:01

I'm trying to do the same....pick up an email attachment within Lotus Notes mailbox.

Is this possible?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-11 : 10:43:02

Hello,
I believe this is more of a design issue. Why jump through all these
hoops?
why are you getting the attachment only to push it to the database?
I assume you need to see the attachment before uploading it to the database, otherwise you would not be getting it to your Inbox? Your Inbox whatever it is Outlook with Exchange backend, or Lotus Notes is for mainly for emails not a temporary holding place for attachments you will never read..unless there are extenuating circumstances..such as no access to ftp etc.
Is the source of the attchment internal or external? Can they not put it to the folder where you eventually will be putting it for the SSIS? all these issue are involved..so there might be a simpler way of doing it..

klubell
Starting Member

4 Posts

Posted - 2009-02-26 : 15:20:11

Unfortunately sometimes you receive data via email from other companies on a daily basis. It seems odd to not want to automate it. In our case, we do not have the choice of retrieving the data via ftp or http.

klubell
Starting Member

4 Posts

Posted - 2009-03-06 : 15:47:42

You can do it via script in Exchange Web Services

klubell
Starting Member

4 Posts

Posted - 2009-03-06 : 18:17:46

I have worked on this all day and it works using Exchange Web Services only for Exchange 2007. You need to create the script task that has a web reference to your exchange 2007 server
usually it is https://mailserver.mycompany.com/EWS/Exchange.asmx in the script task code not the dtsx code, and then you pass variables to specify account, server, from who, and where to place attachments. It also deletes the emails which are from that user and has attachments.

public void Main()

{

//----------------- Setup windows credentials for live server ----

//

string sUserName = Dts.Variables["EmailUserName"].Value.ToString();

string sPassword = Dts.Variables["EmailPassword"].Value.ToString();

string sDomain = Dts.Variables["EmailDomain"].Value.ToString();

string sAuthenticate = Dts.Variables["EmailAuthentication"].Value.ToString(); //Windows, Basic

string sEWS_URL = Dts.Variables["EmailURL"].Value.ToString();

// ------------------- Lets now bind to Exchange

NetworkCredential nc = null;

if (sAuthenticate == "Windows")

{

nc = System.Net.CredentialCache.DefaultNetworkCredentials;

}

else

{

nc = new NetworkCredential(sUserName, sPassword, sDomain);

}

ExchangeWebServices.ExchangeServiceBinding esb = new ExchangeWebServices.ExchangeServiceBinding();

esb.Url = sEWS_URL;

esb.Credentials = nc;

//----------------------- main code ---------------

ExchangeWebServices.FindItemType findItemRequest = new ExchangeWebServices.FindItemType();

findItemRequest.Traversal = ExchangeWebServices.ItemQueryTraversalType.Shallow;

// Define which item properties are returned in the response

ExchangeWebServices.ItemResponseShapeType itemProperties = new ExchangeWebServices.ItemResponseShapeType();

itemProperties.BaseShape = ExchangeWebServices.DefaultShapeNamesType.AllProperties;

findItemRequest.ItemShape = itemProperties; // Add properties shape to request

// Identify which folders to search to find items

ExchangeWebServices.DistinguishedFolderIdType[] folderIDArray = new ExchangeWebServices.DistinguishedFolderIdType[1];

folderIDArray[0] = new ExchangeWebServices.DistinguishedFolderIdType();

folderIDArray[0].Id = ExchangeWebServices.DistinguishedFolderIdNameType.inbox;

// Add folders to request

findItemRequest.ParentFolderIds = folderIDArray;

//Create unread only restriction --------------------------

ExchangeWebServices.RestrictionType restriction = new ExchangeWebServices.RestrictionType();

ExchangeWebServices.IsEqualToType isEqualTo = new ExchangeWebServices.IsEqualToType();

ExchangeWebServices.PathToUnindexedFieldType pathToFieldType = new ExchangeWebServices.PathToUnindexedFieldType();

pathToFieldType.FieldURI = ExchangeWebServices.UnindexedFieldURIType.messageFrom;// .messageIsRead;

ExchangeWebServices.FieldURIOrConstantType constantType = new ExchangeWebServices.FieldURIOrConstantType();

ExchangeWebServices.ConstantValueType constantValueType = new ExchangeWebServices.ConstantValueType();

constantValueType.Value = Dts.Variables["EmailSender"].Value.ToString();// "0";

constantType.Item = constantValueType;

isEqualTo.Item = pathToFieldType;

isEqualTo.FieldURIOrConstant = constantType;

restriction.Item = isEqualTo;

findItemRequest.Restriction = restriction;

// ------------- GetAccessibilityObjectById the stream

Dts.Log("Sending Get Items Request", 999, null);

// ------------- Send the request and get the response

ExchangeWebServices.FindItemResponseType findItemResponse = esb.FindItem(findItemRequest);

Dts.Log("Received Response Get Items", 999, null);

// ------------- read returned

ExchangeWebServices.FindItemResponseMessageType folder = (ExchangeWebServices.FindItemResponseMessageType)findItemResponse.ResponseMessages.Items[0];

ExchangeWebServices.ArrayOfRealItemsType folderContents = new ExchangeWebServices.ArrayOfRealItemsType();

folderContents = (ExchangeWebServices.ArrayOfRealItemsType)folder.RootFolder.Item;

ExchangeWebServices.ItemType[] items = folderContents.Items;

string sText = "";

bool bTrue = true;

foreach (ExchangeWebServices.ItemType curItem in items)

{

sText += "Subject: " + (curItem.Subject.Trim()) + " ";

sText += "DisplayTo: " + (curItem.DisplayTo.Trim()) + " ";

sText += "DateTimeReceived: " + (curItem.DateTimeReceived.TimeOfDay.ToString()) + " ";

sText += "DateTimeReceived: " + (curItem.ItemClass.Trim()) + " ";

int iAttCount = GetFileAttachmentsCount(esb, curItem.ItemId);

sText += "AttachmentCount: " + iAttCount;

sText += "\r\n";

Dts.Log(curItem.Subject, 999, null);

if (iAttCount > 0)

{

GetAttachmentsOnItem(esb, curItem.ItemId, Dts.Variables["CSVDirectory"].Value.ToString());

if (!DeleteItem(esb, curItem.ItemId))

Dts.Events.FireInformation(99, "Delete", "Cannot Delete Item", "", 0, ref bTrue);

}

}

Dts.Log("Items:"+ sText, 999, null);

Dts.TaskResult = (int)ScriptResults.Success;

}

public static int GetFileAttachmentsCount(ExchangeWebServices.ExchangeServiceBinding binding, ExchangeWebServices.ItemIdType id)

{

int iAttachmentCount = 0;

// Use GetItem on the Id to get the Attachments collection

ExchangeWebServices.GetItemType getItemRequest = new ExchangeWebServices.GetItemType();

getItemRequest.ItemIds = new ExchangeWebServices.ItemIdType[] { id };

getItemRequest.ItemShape = new ExchangeWebServices.ItemResponseShapeType();

getItemRequest.ItemShape.BaseShape = ExchangeWebServices.DefaultShapeNamesType.AllProperties;

ExchangeWebServices.PathToUnindexedFieldType hasAttachPath = new ExchangeWebServices.PathToUnindexedFieldType();

hasAttachPath.FieldURI = ExchangeWebServices.UnindexedFieldURIType.itemHasAttachments;

ExchangeWebServices.PathToUnindexedFieldType attachmentsPath = new ExchangeWebServices.PathToUnindexedFieldType();

attachmentsPath.FieldURI = ExchangeWebServices.UnindexedFieldURIType.itemAttachments;

// Add additional properties?

getItemRequest.ItemShape.AdditionalProperties = new ExchangeWebServices.BasePathToElementType[] { hasAttachPath, attachmentsPath };

ExchangeWebServices.GetItemResponseType getItemResponse = binding.GetItem(getItemRequest);

ExchangeWebServices.ItemInfoResponseMessageType getItemResponseMessage = getItemResponse.ResponseMessages.Items[0] as ExchangeWebServices.ItemInfoResponseMessageType;

if (getItemResponseMessage.ResponseCode == ExchangeWebServices.ResponseCodeType.NoError)

{

ExchangeWebServices.ItemType item = getItemResponseMessage.Items.Items[0];

// Don't rely on HasAttachments - It does not mean what you thing it would.

if ((item.Attachments != null) && (item.Attachments.Length > 0))

{

for (int attachmentIndex = 0; attachmentIndex < item.Attachments.Length; attachmentIndex++)

{

ExchangeWebServices.FileAttachmentType almostAnAttachment = item.Attachments[attachmentIndex] as ExchangeWebServices.FileAttachmentType;

if (almostAnAttachment != null)

{

iAttachmentCount += 1;

}

}

}

}

return iAttachmentCount;

}

public static bool DeleteItem(ExchangeWebServices.ExchangeServiceBinding esb, ExchangeWebServices.ItemIdType id)

{

bool bReturn = true;

// Create the DeleteItem request.

ExchangeWebServices.DeleteItemType dit = new ExchangeWebServices.DeleteItemType();

dit.ItemIds = new ExchangeWebServices.BaseItemIdType[1];

dit.ItemIds[0] = id;

// Delete the message.

ExchangeWebServices.DeleteItemResponseType diResponse = esb.DeleteItem(dit);

// Check the result.

if (diResponse.ResponseMessages.Items.Length > 0 &&

diResponse.ResponseMessages.Items[0].ResponseClass == ExchangeWebServices.ResponseClassType.Success)

{

bReturn = false;

}

return bReturn;

}

public static void GetAttachmentsOnItem(ExchangeWebServices.ExchangeServiceBinding binding, ExchangeWebServices.ItemIdType id, string destinationPath)

{

// STEP 1: We need to call GetItem on the Id so that we can get the Attachments collection back

ExchangeWebServices.GetItemType getItemRequest = new ExchangeWebServices.GetItemType();

getItemRequest.ItemIds = new ExchangeWebServices.ItemIdType[] { id };

getItemRequest.ItemShape = new ExchangeWebServices.ItemResponseShapeType();

// For this example, all we really need is the HasAttachments and the Attachment Collection.

// As such, let's just request those props to reduce network traffic.

//

getItemRequest.ItemShape.BaseShape = ExchangeWebServices.DefaultShapeNamesType.IdOnly;

ExchangeWebServices.PathToUnindexedFieldType hasAttachPath = new ExchangeWebServices.PathToUnindexedFieldType();

hasAttachPath.FieldURI = ExchangeWebServices.UnindexedFieldURIType.itemHasAttachments;

ExchangeWebServices.PathToUnindexedFieldType attachmentsPath = new ExchangeWebServices.PathToUnindexedFieldType();

attachmentsPath.FieldURI = ExchangeWebServices.UnindexedFieldURIType.itemAttachments;

// Add these to our additional properties...

//

getItemRequest.ItemShape.AdditionalProperties = new ExchangeWebServices.BasePathToElementType[] { hasAttachPath, attachmentsPath };

// Now make the call

//

ExchangeWebServices.GetItemResponseType getItemResponse = binding.GetItem(getItemRequest);

// getItem returns ItemInfoResponseMessages. Since we only requested one item, we should only

// get back one response message.

ExchangeWebServices.ItemInfoResponseMessageType getItemResponseMessage = getItemResponse.ResponseMessages.Items[0] as ExchangeWebServices.ItemInfoResponseMessageType;

// Like all good, happy and compliant developers, we should check our response code...

//

if (getItemResponseMessage.ResponseCode == ExchangeWebServices.ResponseCodeType.NoError)

{

// STEP 2: Grab the Attachment Ids from our item

ExchangeWebServices.ItemType item = getItemResponseMessage.Items.Items[0];

if (item.HasAttachments && (item.Attachments != null) && (item.Attachments.Length > 0))

{

List<ExchangeWebServices.RequestAttachmentIdType> attachmentIds = new List<ExchangeWebServices.RequestAttachmentIdType>();

for (int attachmentIndex = 0; attachmentIndex < item.Attachments.Length; attachmentIndex++)

{

// For now, let's only consider file attachments instead of item attachments.

//

ExchangeWebServices.FileAttachmentType almostAnAttachment = item.Attachments[attachmentIndex] as ExchangeWebServices.FileAttachmentType;

if (almostAnAttachment != null)

{

// VERY IMPORTANT! The attachment collection returned by GetItem only has meta data

// about the attachments, but DOES NOT INCLUDE THE ACTUAL CONTENT. We must use

// GetAttachment to get the actual attachment.

//

ExchangeWebServices.RequestAttachmentIdType requestId = new ExchangeWebServices.RequestAttachmentIdType();

requestId.Id = almostAnAttachment.AttachmentId.Id;

attachmentIds.Add(requestId);

}

}

// now that we have all of the attachment ids, let's make a single GetAttachment call to

// get them all in one shot.

//

ExchangeWebServices.GetAttachmentType getAttachmentRequest = new ExchangeWebServices.GetAttachmentType();

// Oddly enough, just create an EMPTY (non-null) attachment response shape.

//

getAttachmentRequest.AttachmentShape = new ExchangeWebServices.AttachmentResponseShapeType();

getAttachmentRequest.AttachmentIds = attachmentIds.ToArray();

ExchangeWebServices.GetAttachmentResponseType getAttachmentResponse = binding.GetAttachment(getAttachmentRequest);

// Now, here we asked for multiple items. As such, we will get back multiple response

// messages.

foreach (ExchangeWebServices.AttachmentInfoResponseMessageType attachmentResponseMessage in getAttachmentResponse.ResponseMessages.Items)

{

if (attachmentResponseMessage.ResponseCode == ExchangeWebServices.ResponseCodeType.NoError)

{

// We only asked for file attachments above, so we should only get FileAttachments.

// If you are really paranoid, you can check for null after this again.

//

ExchangeWebServices.FileAttachmentType fileAttachment = attachmentResponseMessage.Attachments[0] as ExchangeWebServices.FileAttachmentType;

// Now, just save out the file contents

//

using (FileStream file = File.Create(

Path.Combine(destinationPath, fileAttachment.Name)))

{

file.Write(fileAttachment.Content, 0, fileAttachment.Content.Length);

file.Flush();

file.Close();

}

}

}

}

}

}

}

sofsol
Starting Member

1 Post

Posted - 2009-03-10 : 22:22:32

That's a great effort, klubell, and I only wish I was using Exchange 2007, but we are still on 2003. Does anybody have an idea for extracting attachments with 2003? (Why? Because we have some outside companies who will only send daily data updates by e-mail.)

minnie.schurr
Starting Member

1 Post

Posted - 2010-12-06 : 19:03:44

Wow klubell, you must be a .NET guru; I wish one day I will be able to write that complex code.

Hope the code is useful for me one day too. as I've not been using Web Service and no idea where/how to create Web Service hence ready to be called.

Cheers

Min

mobsite
Starting Member

1 Post

Posted - 2012-08-29 : 16:17:15

@klubell - awesome work and thank you for sharing! 3+ years later, still on exchange 2007 and other than a few tweaks to the code that looked to be more copy/paste errors than anything, it works fantastic. Additional notes that may not be obvious for relative noobs (me included)...

Create package level string variables:
EmailUserName
EmailPassword
EmailDomain
EmailAuthentication - Windows or Basic auth
EmailURL
CSVDirectory - file attachment output directory
EmailSender - filter for emails to gather from inbox

In script task, after copying in this code, here's some quick hints...

- remove a closing squiggly bracket at the very end (it was the class closer)
- when creating web reference, be sure to replace default name to: "ExchangeWebServices"

add the following using statements:


using System.Net;
using System.Collections.Generic;
using System.IO;

Be advised that the DeleteItem method will "hard" delete the email, so don't expect to be able to find it in the "Deleted Items" folder.

I believe the DeleteItem response handling is backwards... should say:


// Check the result.

if (diResponse.ResponseMessages.Items.Length > 0 && 
	diResponse.ResponseMessages.Items[0].ResponseClass == ExchangeWebServices.ResponseClassType.Success)
	{
		//bReturn = false; // this was the original, but i think this is incorrect???
		bReturn = true;
	}
else
	bReturn = false;

return bReturn;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources