Introduction
In my experience there are a number of customers that send daily/ monthly data, via email and are unwilling to change this. I wanted to automate this, so that I did not need to manually put a file in a certain location whenever I recieve this mail.
I looked online for a solution and some were available but nothing was free. and the free stuff didn't support smtp. So when i came accross the reference code I modified it for my script task...
What the Code does
SSIS Script task code to download Outlook attachment recieved from user test@test.com and save them to local or network file location c:\Test and move email to a completed folder in outlook called test.
Requirements
- SQL server 2008 or 2012
- Visual Studio Premium (Need MS SDK that comes with VS 2010 Premium)
- Outlook (Tested on 2007 and 2010)
Restrictions
Fails when it sees meeting invites (only tested this with outlook 2007)
In production, had to use AD account with primary mailbox instead of service account.
Requires outlook installed on server
Using the code
- Install outlook on machine with SSIS and setup outlook mail.
- Create Script Task, Script Language: Microsoft Visual Basic 2008
- Add Microsoft Outlook Reference
VS 2008
Edit Script Task-> Data Sources -> Add New Data Source-> Object-> Next-> Add Reference-> COM-> Microsoft Outlook 12.0 Object Library
VS 2010
Edit Script Task-> Project -> Add Reference... -> COM-> Microsoft Outlook 12.0 Object Library
- Replace Script Scode with below- Edit test@tester.com and c:\test\
- Run and hopefully all will work well.
Imports System
Imports System.Data
Imports System.Reflection
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim oApp As Outlook.Application = New Outlook.Application() Dim oNS As Outlook.NameSpace = oApp.GetNamespace("mapi") Dim Attachment As Outlook.Attachment
Dim FileName As String
Dim oInbox As Outlook.MAPIFolder = oNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
Dim oItems As Outlook.Items = oInbox.Items
Dim oDestFolder As Outlook.MAPIFolder = oInbox.Folders("test")
Console.WriteLine("Total : " & oItems.Count)
Console.WriteLine("Total Email : " & oItems.Count)
Dim oMsg As Outlook.MailItem
Dim i As Integer
Dim j As Integer
i = 0
j = 1
For i = 1 To oItems.Count
oMsg = oItems.Item(i)
If (oMsg.SenderName = "testAtest.com") Then
For Each Attachment In oMsg.Attachments
MessageBox.Show(Attachment.FileName)
FileName = "c:\Test\" & Attachment.FileName
Attachment.SaveAsFile(FileName)
j = j + 1
Next
oMsg.Move(oDestFolder)
End If
i = i + 1
Next
oNS.Logoff()
oApp = Nothing
oNS = Nothing
oItems = Nothing
oMsg = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
End Class