SSIS Job Stopped Working After Change to Office 365 on Server

  • rdsb_2170

    SSC Eights!

    Points: 812

    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

  • Phil Parkin

    SSC Guru

    Points: 243847

    What is the text of the error message you receive when running this?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • frederico_fonseca

    SSChampion

    Points: 14219

    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.

  • rdsb_2170

    SSC Eights!

    Points: 812

    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 !!

  • Phil Parkin

    SSC Guru

    Points: 243847

    rdsb_2170 wrote:

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • rdsb_2170

    SSC Eights!

    Points: 812

    My apologies Phil. I only saw the signature message on my phone.

     

  • frederico_fonseca

    SSChampion

    Points: 14219

    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)

    Attachments:
    You must be logged in to view attached files.

Viewing 7 posts - 1 through 7 (of 7 total)

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