The Job Failed. the Job Was Invoked by User<User>. the Last Step to Run Was Step1

The job failed. The job was invoked by useruser. The last step to run was step1

The screen captures are great but the detail is going to be on the sublines, so in the first picture, where you have expanded the [+] sign and it says "Executed as user X. Unable to open Step output file"

If you select that row, there is generally more detail displayed in the bottom pane.

General trouble shooting for something working in BIDS/SSDT but not in SQL Agent

That said, generally when something works in BIDS/SSDT and does not in the SQL Agent, then the first thing to look at is the difference in permissions. You are running the package in visual studio and your credentials are used for

• File System
• Database (unless a specific user and pass are provided)
• General SaaS (Send Mail Task will use some mail host to transfer the email)

Running things in a SQL Agent job can complicate things as you now have the ability for each job individual job step to run under the SQL Agent account or a delegated set of credentials your DBA has established.

Further complicating matters are network resources---my K: drive might be mapped to \\server1\it\billinkc whereas the SQL Server Agent Account might have it mapped to \\server2\domainAccount\SQLServer\ or it might be entirely unmapped.

As Gowdhaman008 mentioned, there can also be a 32 vs 64 bit mismatch. Generally this is specific to using Excel as a source/destination but also rears its head with other RDBMS specific drivers and/or ODBC connections for said resources.

Specific to your example

Based on the fragment of the error message, my primary assumption is that the account CORP\CORP-MAD$ does not have access to the location where the file has been placed. To resolve that, ensure the MAD$ account has read/write access to the location the Happy files have been placed. Since that account ends in $, it might only exist on the computer where SQL Agent is running. If it's accessing a network/SaaS resource, you might need to create an explicit Credential in SQL Server (under Security) and then authorize that Credential for SSIS subtasks.

A secondary, less likely, possibility is that the files don't exist and that's just a weird Send Mail error. I know I still get plenty of hits on The parameter 'address' cannot be an empty string even though an email address is provided.

The job failed the job was invoked by schedule 1016. The last step to run was step1

As the comments indicate, 1016 is not an error message. Instead, it's the identifier for a schedule. That's why you won't find another question about it.

Let's figure out what the failing job is and what it's expected to do.

Using SSMS, connect to the server in question and run the following query

SELECT
    SJ.name AS JobName
,   JS.step_id
,   JS.step_name
,   JS.subsystem
,   JS.command
,   JS.server
,   JS.proxy_id
,   JS.step_uid
,   SP.name AS proxy_name
,   C.credential_id
,   C.name AS credential_name
,   C.credential_identity
FROM
    msdb.dbo.sysjobschedules AS SJS
    INNER JOIN
        msdb.dbo.sysjobs AS SJ
        ON SJ.job_id = SJS.job_id
    INNER JOIN
        msdb.dbo.sysjobsteps AS JS
        ON JS.job_id = SJ.job_id
    LEFT OUTER JOIN
        msdb.dbo.sysproxies AS SP
        ON SP.proxy_id = JS.proxy_id
    LEFT OUTER JOIN
        msdb.sys.credentials AS C
        ON C.credential_id = SP.credential_id
    
WHERE
    EXISTS
(
    SELECT
        *
    FROM
        msdb.dbo.sysschedules AS SS
    WHERE
        SS.schedule_id = SJS.schedule_id
        AND SS.schedule_id = 1016
)
ORDER BY 1,2;

This query will find all the jobs that are members of the job schedule id 1016. Jobs can share a schedule so if you find more than one job (column 1) listed, then you'll need to look at the job execution to figure out which one failed.

This query is going to expose all the data that supports the job step definition so you'll be looking for all the places where step_id = 1 since your error message indicates The last step to run was step1 Unless that's the value of step_name then filter for that.

You indicate the SSIS package runs fine as you so the command column should be patched into your question as that identifies "what" the jobs is doing and can provide a reference point to "you" running the package.

Along with that, proxy_name, credential_name, credential_identity if not null indicate we have credentialed proxies in place which add another layer to identifying where the package might have gone wrong.

If this package executes from the SSISDB, then you'll want to dump the error log into your question as well.

-- Find all messages associated to the last failing run
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
FROM
    SSISDB.catalog.operation_messages AS OM
WHERE
    EXISTS    
    (  
        -- Find the last failing operation
        -- lazy assumption that biggest operation
        -- id is last. Could be incorrect if a long
        -- running process fails after a quick process
        -- has also failed
        SELECT 
            MAX(OMI.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OMI
        WHERE
            OMI.message_type = 120
            AND OM.operation_id = OMI.operation_id
    );

You can use the native reports (All Executions) to find the failing execution of your package. That first column, ID is the operation_id so you can replace the EXISTS clause with a basic OM.operation_id = 10 and if you only want the errors, filter for AND message_type = 120

SQL Server Agent Job Invoked

Have you looked into Job Properties -> Steps -> Job Steps -> Advanced tab? There is a setting for the number of Retry attempts, and a Retry interval in minutes. There is also an "On failure action" dropdown, which can be set to do unexpected things such as go on to the next step.

Alternatively, maybe is there a different job that is invoking this job in odd ways? Perhaps it has a Step or a Schedule called "Alerts 2", or is called that itself?

---

Related Topics