none
SQL Server Agent job fails , but works fine when user stays logged in RRS feed

  • Question

  • I have a SQL Server job that runs a SSISDB package. The package does some SQL database connection and creates an excelt file as output. Finally it runs a VB 2015 script that opens the created excel file and rcopies sheets from that to another excel file and then a macro is run. The jobs runs fine as a SQL server agent job while I'm still logged onto the server . However when I log out the VB Script task fails with the message ' Script Task Error: Exception has been thrown by the target of an invocation. '.

    help !


    Tuesday, August 13, 2019 9:58 AM

All replies

  • Hi pdr,

    There are several possibilities this error will appear.

    Hope it helps.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 14, 2019 3:22 AM
  • However when I log out the VB Script task fails

    I can't imagine that there could be a relation wheter you are logged in or not.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 14, 2019 5:26 AM
  • Thanks for these Zoe. I had tried with a proxy account, and also changed it so that the excel files were on the local disk. I believe it's something to do with SQL Server job not being able to launch excel. I'm going down looking at the DCOM settings for Excel at the moment.  I'll revisit some of these suggestions .. thanks.

    Wednesday, August 14, 2019 8:44 AM
  • The evidence would suggest otherwise ..  I set the job to retry 3 times with 5 minute intervals between retries . I log out of the machine while the job runs at the set time. When i log back in I can see that the first attempt has failed ..  while i'm logged in the second attempt will complete successfully.. don't know what else I can say .
    Wednesday, August 14, 2019 8:48 AM
  • Hi pdr,

    May I know why you log out of the server?

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 14, 2019 8:52 AM
  • Hi pdr,

    The following links might be helpful.

    Please have a try.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, August 14, 2019 8:58 AM
  • Its a server used by various developers. There are limitations on the number who can access at the same time so the general rule is you log out when you're not using it. 
    Wednesday, August 14, 2019 10:35 AM
  • It must be due to the account rights that is executing the package, it by default is a network account that the Agent is running under and therefore cannot do much - you will need to use a domain account based proxy

    http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 14, 2019 10:46 AM
  • thanks, create a proxy account based on my account and set the step to use the proxy. Made no difference. 

    I've create a stripped doen script , so all it does is open and close a local excel file. Added some logging to find out exactly where it gets to before it falls over. 

    It falls over on this line in the VB script.

    xlApp = New Excel.Application

    DCOM premission for Excel  ( launch and Activation ) are greyed out ..  I can select customize but when I go back in they're back to use default. Anyone know what I need to do .. assuming this may be the solution to the problem.

    

    Wednesday, August 14, 2019 1:46 PM
  • Ensure Excel is installed

    Arthur

    MyBlog


    Twitter

    Wednesday, August 14, 2019 1:49 PM
    Moderator
  • Excel is installed, otherwise it wouldn't work when I'm logged into the server, would it ?
    Wednesday, August 14, 2019 2:25 PM
  • Ensure Excel is installed for all users.

    Arthur

    MyBlog


    Twitter

    Wednesday, August 14, 2019 3:32 PM
    Moderator
  • Hi pdr,

    This it is due to some DCOM permission settings for Microsoft Excel Application in Component Services. We need to run the command MMC comexp.msc, go to the properties of Microsoft Excel Application, under Identity, change it to The Interactive User from The Launching User (which is set by default). After making this change you might be able to run the package as a scheduled SQL job successfully. However, there is still 1 little caveat when we are on x64 Windows 2008 (R2 also) Operating System where Component Services launched in 64 Bit does not show Microsoft Excel Application. In such a scenario we need to launch Component Services in x86 mode with the command MMC comexp.msc /32, rest of things remain the same.

    If the account which is running EXCEL is administrator then this will work:

    For 64-bit (x64), create this folder: C:WindowsSysWOW64configsystemprofileDesktop

    For 32-bit (x86), create this folder: C:WindowsSystem32configsystemprofileDesktop

    Otherwise To resolve this issue follow these steps:

    1. Login to your Server as a administrator.

    2. Go to "Start" -> "Run" and enter "MMC comexp.msc /32".

    3. Go to the properties of Microsoft Excel Application, under Identity, change it to The Interactive User from The Launching User (which is set by default).

    4. Go to the properties of Microsoft Office Excel 2007 Workbook, under Identity, change it to The Interactive User from The Launching User (which is set by default).

    5. Go to Security tab for Microsoft Excel Application and select Customize for " Launch and Activation Permissions" and add ACCOUNT (under which EXCEL is running) to it and give it "Local launch" and "Local Activation" permission.

    6. Go to Security tab for Microsoft Office Excel 2007 Workbook and select Customize for " Access Permissions " and add ACCOUNT (under which EXCEL is running)  to it and give it "Local Access" permission.

    Please have a try.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, August 15, 2019 1:13 AM
  • Thanks Zoe, I don't seem to have an entry in component services for 'Microsoft Office Excel 2007 Workbook'. I've set the entry for 'Microsoft Excel Application' as you've described above . I'm still getting the same issue.. 

    thanks

    Phil. 

    Thursday, August 15, 2019 10:27 AM
  • Hi pdr,

    Another way you could try:

    Under component services -> Computers -> My Computer -> DCOM Config + Microsoft Excel application -> properties -> Identity Tab -> Select the Interactive User -> Ok.

    And in the code make this change to explicitly close the file which was opened app.Workbooks.Close();

    Hope it helps.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 2:36 AM
  • Thanks Zoe, 

    So, I'm getting closer to the cause of the problem , but not the solution .. :-( 

    Seems to be to do with the user that is running SQL server Agent. 

    I can log in as that user and open excel. However, if I open Visual Studio and just run a package that just runs a script. The script opens excel then closes.  It fails with the 'Exception has been thrown by the target of an invocation' message. The same package works when logged in as me .. 

    Monday, August 19, 2019 9:17 AM
  • Hi pdr,

    Please refer to Upgrading Script Tasks in SSIS. Target of Invocation on Script Task.

    Hope it helps.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 19, 2019 9:53 AM