Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL SSIS package x SQL Server Agent error RE: SSIS package x SQL Server Agent error

  • April 22, 2018 at 6:19 am

    #1987794

    cvbarbosa93 33845 - Friday, April 20, 2018 9:17 PM

    Hello!

    I'm experiencing a SSIS package error during execution. The package runs ok without error and runs the proccess below:
    1. Create a Excel file;
    2. Input data into Excel file;
    3. Format sheets with bold, fonts, colors and more...
    When I run this package over Visual Studio, no error occurs. But I have no same situation when I try to run over SQL Server Agent. The error returned is below:

    Date        4/21/2018 12:12:27 AM
    Log        Job History (DTS - Homologação - Envia Notificação)

    Step ID        1
    Server        #########
    Job Name        DTS - Homologação - Envia Notificação
    Step Name        DTS - EXECUTA
    Duration        00:00:02
    Sql Severity    0
    Sql Message ID    0
    Operator Emailed    
    Operator Net sent    
    Operator Paged    
    Retries Attempted    0

    Message
    Executed as user: XXXXXXX. Microsoft (R) SQL Server Execute Package Utility Version 13.0.4474.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved.  Started: 12:12:27 AM Error: 2018-04-21 00:12:29.66  Code: 0x00000001  Source: Script Task  Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:12:27 AM Finished: 12:12:29 AM Elapsed: 2.281 seconds. The package execution failed. The step failed.

    Do anybody experiencied this error also? I need some help with it...

    Based on what you mention the script is doing I would guess that you are using the Office Interop assemblies - this being the case it would also mean you need to have a copy of Office installed on your server  which is not supported - and would also require you to have a second license of office bought for each user that directly or indirectly uses that server.

    Script Task Description: Exception has been thrown by the target of an invocation. - this will normally be
     - missing dll/assembly
     - incorrect dll/assembly version
     - 32/64 bit issue

    If it is just 32/64 bit issue then you can "fix" it by forcing dtexec to run in either 32/64 as applicable - most of the times you do wish to have it on 64 bit, even for excel - but if on your own PC 32 bit may be the requirement.

    If it is a different issue you should first try and identify the correct error.
    this can be done by using a try/catch block around your code and firing a event to log the error
          try
        {
          Dts.Log("Rows processed: " + rowsProcessed.ToString(), 0, emptyBytes);
          Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {
          //An error occurred.
          Dts.Events.FireError(0, "Script Task Example", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
          Dts.TaskResult = (int)ScriptResults.Failure;
        }

    https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/raising-events-in-the-script-task?view=sql-server-2017 has more info on these.