May 17, 2019 at 4:47 pm
We have a SSIS job that archives reports, by scraping the attachment from the email and saving it to the appropriate folder.
I recently had to update my password, as part of our security policy. After I updated the password, I logged onto the server and updated the password in Task Scheduler. When I tried to update my account in Outlook (on the server) I discovered that IT has removed outlook....and replaced it with Office 365. No the timing of all this is strange, but basically everything was working fine on this SSIS job until I did the password change.
I've tried several troubleshooting measures....too many to list. My next question is this -
Do I have to change the vb script task to refer to Office 365 instead of "Outlook.Application.16"? If so, what do I change it to?
Imports Microsoft.Office.Interop
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>
<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()
On Error GoTo ErrorHandler
Dim bt(0) As Byte
Dim myOlapp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myItem As Outlook.MailItem
Dim myAttachment As Outlook.Attachment
Dim vFolder As Outlook.MAPIFolder
Dim oDestFolder As Outlook.MAPIFolder
myOlapp = CType(CreateObject("Outlook.Application.16"), Outlook.Application)
myNameSpace = myOlapp.GetNamespace("MAPI")
......more script below this.
Any help is most appreciated....this is causing me to pull my hair out 🙁
Cervello
May 17, 2019 at 6:03 pm
What is the text of the error message you receive when running this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2019 at 9:52 pm
IT removing Outlook from the server was the correct thing to do - Outlook is not a server component, is not supported from Microsoft while running on a server as you were and you would most likely be breaking licensing terms.
My suggestion there is to stop using outlook on your server and code using the Microsoft Exchange Web Services Managed API for it https://docs.microsoft.com/en-us/exchange/client-developer/exchange-web-services/start-using-web-services-in-exchange
This will work with Office 365 as well as previous versions of Exchange.
And setup a non interactive account to use to process the email instead of your own
With the API you can easily access any mailbox that the user created above has been given access to.
and for info I do use this to access a mail box, get all attachements and save them to configurable destinations - including unziping/renaming attachement files where required.
May 20, 2019 at 3:19 pm
Hi Phil,
I can assure you I did a google search before reaching out to the forum. If you can't assist with the inquiry, then please don't insult users by implying we wouldn't employ the basic research most of us do naturally.
If you can't provide a helpful recommendation keep it to yourself !!
May 20, 2019 at 5:33 pm
Hi Phil, I can assure you I did a google search before reaching out to the forum. If you can't assist with the inquiry, then please don't insult users by implying we wouldn't employ the basic research most of us do naturally. If you can't provide a helpful recommendation keep it to yourself !!
Cervello, please look at my post again, it asks a reasonable question.
The part you are referring to is in my signature and is not directed at you. I added it to my signature because so many people do ask questions which are easily answered by reading through material which is easily found via a search.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 20, 2019 at 9:59 pm
My apologies Phil. I only saw the signature message on my phone.
June 5, 2019 at 8:50 pm
At OP's request I'm posting here some code of how this can be done using Exchange Web Services.
the powershell code attached is being used currently on my shop (with a few more additions to it that I had to remove) and expects some configuration tables to be setup to determine where to save the attachments - that can be removed if location is always going to be the same.
Same config tables also specify the type of treatment to do with the attachments
As examples I have left the case of Nexpose/Rapit 7 which send emails from its automated report system always with the same name - report.zip. The body of the email contains the name of the report so this can be parsed and the name extracted and used as the real filename to use.
some cases we also receive just a standard file that can be extracted directly, or other times we get a zip file and this has to be extracted before it can be processed by our SQL Server processes.
Security requirements: As this is accessing an Exchange server we need to use a real AD account credentials to connect to the server - as of Windows Server 2016 and lower GMSA's do not work - Windows 2019 seems to have added this functionality but I have not tried it yet.
As such a credential object must be created with normal username/password to pass to the server connection.
For this example I've left a sample of it on the script - on production this should be retrieved from a safe source.
(Attachment as .txt to avoid being blocked by site software)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply