locked
SSIS Package runs OK manually but not as a scheduled Job. Error Message : "exception has been thrown by the target of an invocation" RRS feed

  • Question

  • I've created a SSIS package that calls the access dll and fires off 2003 access reports, saves them as PDF's and emails them off.

    Now this works fine when I run it manually, but when I schedule and fire off a job I get a very vague error "exception has been thrown by the target of an invocation".

    I have copied the access dll to the GAC and .net framework v2.0.50727 but still no luck.

    I'm using Bull zip PDF printer and those DLL's are also in the GAC

    Not sure was else to do? I've scoured the web with no answers.

    Please help.

    Tuesday, April 21, 2015 7:07 PM

Answers

  • Ok so what we can do as a hot and trial is the account which has access to the folder locations where you create these files, do you have a specific location where you create these files on the script task, if not then try giving admin access to the C:\Temp folder for the proxy account.

    b) If this doesnt work how about using your user name as password on the proxy account, and then trying this way we know that the problem is with the proxy account access levels or something else. As you executed with your account earlier then you should be able to execute it with proxy running as your account.

    Finally to list down the error this is something we could try and i havent tried it myself

    a) in your script task do a try catch something like Catch ex As DtsException

    b) from catch throw this exception something like

    Try

    {}

    catch( ex as DtsException)

    {

    throw ex

    }

    I havent tried it but if you do this you should be able to catch the exception on DTS side i.e. on System::ErrorCode,System::ErrorDescription,System::SourceDescription

    try writing these error code to a file on the error event for package this would help us jot it down the exact error


    Abhinav http://bishtabhinav.wordpress.com/

    Friday, April 24, 2015 8:31 PM

All replies

  • 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 domion account based proxy

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


    Arthur

    MyBlog


    Twitter

    Tuesday, April 21, 2015 8:06 PM
  • Hi Arthur,

    I followed the steps and associated the administrator to a proxy account and made the SSIS step use the proxy.

    Still getting the same error.

    Graham.

    Tuesday, April 21, 2015 8:51 PM
  • In SSMS Object Explorer, within Integration Services, right click on your package and choose Package Roles, what do you see?
    Tuesday, April 21, 2015 9:00 PM
  • Give us an example when you say it works, how it works.

    It might be related to 32 bit and 64 bit, but you would have to show us how is it succesfull. Is it BIDS where its run fine?


    Abhinav http://bishtabhinav.wordpress.com/

    Tuesday, April 21, 2015 9:00 PM
  • @AB82 - In BIDS I've set Run64BitRuntime to False and the package executes as expected. I get the error when I schedule the package to run as a scheduled job. It executes but fails. I've set the step in the job to run as 32-bit runtime.

    @TheBrenda - I cannot find the Roles of option you mention. In SSMS object explorer, I expand Integration Services -> SSISDB -> MyProjectName -> Projects -> My SSIS Package Name -> Packages -> then right-click on my package

    

    Tuesday, April 21, 2015 9:19 PM
  • I am not familiar with ssisdb. I look at my packages in SSMS, Integration Services -> stored packages -> msdb. 

    when you right click on you package, what is on your context menu? Do you see Package Roles ...? Do you see Run Package...? What happens when you run the package from there?

    Tuesday, April 21, 2015 9:46 PM
  • to the left of this thread there is "top related threads" and i see several with your error. have you looked at those?
    Tuesday, April 21, 2015 10:20 PM
  • @TheBrenda - Great, some progress :) I found my package under msdb and was able to manually execute my package as 32 bit runtime successfully. 

    So that means that the scheduled job failure must be a permissions thing. I've looked at the "related" threads, but they don't relate except for the one about creating a proxy, which I've done.

    I have other scheduled jobs that run and I haven't had an issue before. The only difference in this package is that I'm calling the access and bull zip printer dll's.

    Have a look at my proxy setup. I've created some screenshots http://www.touchsms.co.za/doc1.docx

    Wednesday, April 22, 2015 5:58 AM
  • Hi,

    Enter "Job step properties" (your last screenshot in a word file).

    There enter "Configuration" -> "Advanced"

    and check there "32-bit runtime"

    It should help.


    • Edited by xjomanx Wednesday, April 22, 2015 7:10 AM
    Wednesday, April 22, 2015 7:09 AM
  • @xjomanx - I already have 32-bit runtime enabled but still failing
    Wednesday, April 22, 2015 9:01 AM
  • Then make an "Execution report"

    Services -> SSISDB -> MyProjectName -> Projects -> My SSIS Package Name -> Packages -> then right-click on my package -> "All executions"

    In report choose fail execution and find Error there.

    Wednesday, April 22, 2015 9:55 AM
  • I have done that (I find the error reporting to be real rubbish). The report doesn't tell me much except that "an exception has been thrown by the target of an invocation" which is not useful at all.

    I've tried a try catch in my SQL task but it seems the scheduled job does not even get to that point. I've tried an event handler but that also just returns the same error message.

    Is there a way to obtain more detailed error reporting? 

    Wednesday, April 22, 2015 12:16 PM
  • Check, can WAR-DBN-WEB03\Administrator access to the file, you are using in your package. The most of mistakes "an exception has been thrown by the target of an invocation" are firing, because user can't access to the file.

    Wednesday, April 22, 2015 3:01 PM
  • This is mostly likely a DCOM error.  By default, you cannot run Office applications from SQL Agent because Microsoft does not support running them as a background application.  They are designed to be used with an interactive user.  To change this, you need to go into the server Administrative Tools / Component Services / DCOM Config and find the entry for Microsoft Access Application.  Right click and select Properties, then click the Identity tab.  Select 'The interactive user'.

    If you plan to use a lot of 'Office Automation' you might want to read this:

    Enabling Office Automation

    Just be aware that it is unsupported.

    • Edited by Bendare2 Wednesday, April 22, 2015 3:13 PM
    • Proposed as answer by v.castro92000 Wednesday, February 6, 2019 1:25 PM
    Wednesday, April 22, 2015 3:11 PM
  • @bendare2 - I cannot find the MS app entry in DCOM config.

    xjomanx - I've given Administrator user full admin rights. Is there anything specific I need to do to grant specific access?

    Thanks for your assistance so far everyone - This is pretty painful. If I could just somehow get a better description of the error it would help tremendously.

    Wednesday, April 22, 2015 5:57 PM
  • Ok going back to my previous questions, so the job when scheduled it fails what happens when you just right click and say execute on the job?

    How have you created the job? using Command line or simply SSIS Package, maybe a repetetive questions but what does the 32 bit runtime property looks like did  you set it?

    As far as the error reporting goes it seems you have a script task which is failing and i would assume Main inside the script task either returns 0 or 1 based on success or failure thats why this generic message..


    Abhinav http://bishtabhinav.wordpress.com/


    • Edited by AB82 Thursday, April 23, 2015 12:49 PM
    Thursday, April 23, 2015 12:46 PM
  • @AB82

    When schedule the job it fails. When I run the job manually it succeeds.

    The job is simply a SSIS package. In the Job step the "Use 32 bit runtime" flag is ticked.

    Would you mind using Team Viewer to log into my machine to see if you could spot something? I'd be willing to pay for your time.
    Thursday, April 23, 2015 4:18 PM
  • Does the package actually launch Access, even briefly, when you execute it manually?

    • Edited by Bendare2 Thursday, April 23, 2015 6:01 PM
    Thursday, April 23, 2015 6:00 PM
  • So what are the roles?  you said that you could find your package in Integration Services -> SSISDB -> MyProjectName -> Projects -> My SSIS Package Name -> Packages -> then right-click on my package -> roles?

    I guess that you are the creator of the package? what happens if someone else tries to run it from Integration Services -> SSISDB -> MyProjectName -> Projects -> My SSIS Package Name -> Packages -> then right-click on my package -> RUN.

    Do you have any vbscript? or is it pure SSIS objects?

    Thursday, April 23, 2015 10:35 PM
  • Ok so what we can do as a hot and trial is the account which has access to the folder locations where you create these files, do you have a specific location where you create these files on the script task, if not then try giving admin access to the C:\Temp folder for the proxy account.

    b) If this doesnt work how about using your user name as password on the proxy account, and then trying this way we know that the problem is with the proxy account access levels or something else. As you executed with your account earlier then you should be able to execute it with proxy running as your account.

    Finally to list down the error this is something we could try and i havent tried it myself

    a) in your script task do a try catch something like Catch ex As DtsException

    b) from catch throw this exception something like

    Try

    {}

    catch( ex as DtsException)

    {

    throw ex

    }

    I havent tried it but if you do this you should be able to catch the exception on DTS side i.e. on System::ErrorCode,System::ErrorDescription,System::SourceDescription

    try writing these error code to a file on the error event for package this would help us jot it down the exact error


    Abhinav http://bishtabhinav.wordpress.com/

    Friday, April 24, 2015 8:31 PM
  • Is your file located locally on computer, when SQL server run a task, or it located on some server in the network?
    Monday, April 27, 2015 6:38 AM
  • @Bendare2

    Thanks for the post! This turned out to be the solution to my version of the problem, just that I had to change the setting for "Microsoft Excel Application" instead of Access. 

    • Edited by Saubyj Tuesday, March 27, 2018 11:20 PM
    Tuesday, March 27, 2018 11:05 PM