ETL scheduled job failing with error

Mohammed Abdul Majeed Khan 41 Reputation points
2023-03-14T06:55:25.17+00:00

User's image

An ETL job schdule is failing to run. I am trying to bring data from server outside my domain. the admin on server LMN which is outside my domain created a login credentials (user efg)

when I run the job manually its successful. but on shcedule, it fails. here is the error I am getting.

Message Executed as user: OTH\abc. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 4:40:01 PM Error: 2023-03-13 16:40:02.56 Code: 0xC0202009 Source: FECLoad Connection manager "BAH DC_Data" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'efg'.". End Error Error: 2023-03-13 16:40:02.57 Code: 0xC020801C Source: Load FEC BAH to STG FEC BAH DC_Location_Info [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "BAH DC_Data" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2023-03-13 16:40:02.57 Code: 0xC0047017 Source: Load FEC BAH to STG SSIS.Pipeline Description: FEC BAH DC_Location_Info failed validation and returned error code 0xC020801C. End Error Error: 2023-03-13 16:40:02.57 Code: 0xC004700C Source: Load FEC BAH to STG SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2023-03-13 16:40:02.57 Code: 0xC0024107 Source: Load FEC BAH to STG Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:40:01 PM Finished: 4:40:02 PM Elapsed: 1.422 seconds. The package execution failed. The step failed.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,469 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 41,571 Reputation points
    2023-03-14T06:59:19.74+00:00

    Login failed for user 'efg'

    As the error message says.

    Can you connect to that SQL Server using SSMS with the same credentials?


  2. ZoeHui-MSFT 33,781 Reputation points
    2023-03-15T02:43:15.52+00:00

    Hi @Mohammed Abdul Majeed Khan,

    Please set up a proxy account and then use the account to run the agent job for a try.

    A detailed tutorial you may take a reference to:

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

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.


  3. Mohammed Abdul Majeed Khan 41 Reputation points
    2023-04-09T08:14:06.9333333+00:00

    Hi @ZoeHui-MSFT I made a proxy account and followed all the steps in your suggested blog but still job fails. The error is the following::

    Message Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 4). The step failed.

    0 comments No comments