none
SSIS Package failing in SQL Agent Job RRS feed

  • Question

  • I'm getting this below error  in SQL Agent job, The job has a parent & Child package  , the parent package runs locally and child package is executing its tasks on another server . Parent package has no issues but this error is triggered for child package   . Both servers can be connected via SSMS,RDC. Any help appreciated. 


    Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  1:35:05 PM  Error: 2019-09-16 13:35:50.17     Code: 0xC0202009   

     Source: ChildPackage Connection manager "TestConn"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Login timeout expired".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005

     Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".  End Error  Error: 2019-09-16 13:35:50.17     Code: 0xC020801C     Source: Process ChildPackage Changed Dimension ChildPackage [2]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "TestConn" 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: 2019-09-16 13:35:50.17     Code: 0xC0047017     Source: Process ChildPackage SSIS.TestConn     Description: Changed Dimension ChildPackage failed validation and returned error code 0xC020801C.  End Error  Error: 2019-09-16 13:35:50.18     Code: 0xC004700C     Source: Process ChildPackage SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2019-09-16 13:35:50.18     Code: 0xC0024107     Source: Process ChildPackage     Description: There were errors during task validation.  End Error  Error: 2019-09-16 13:35:50.19     Code: 0xC00220E4     Source: Execute ChildPackage package      Description: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.  .  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:35:05 PM  Finished: 1:35:50 PM  Elapsed:  45.11 seconds.  The package execution failed.  The step failed.



    SANTHOSH KUMAR

    Tuesday, September 17, 2019 5:29 AM

All replies

  • What account is used for executing the child package and particularly the steps which execute the code against the other server?

    Does the account have required access to the other server

    The recommended approach is to define a prosy account for use by the package and give it required access in the other server within database and also within other folders etc which the package uses within its tasks

    And inside SQL Agent set Run As option to proxy account for the steps which calls this package

    see

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


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 17, 2019 5:44 AM
  • Hi SANTHOSH,

    • Try to set DelayValidation to TRUE for all of the Data Flow Tasks in the child package.
    • If it don't work, then try to set ValidateExternalMetaData to FALSE in the properties within each Data Flow Task.
    • Take a look at the topic "Setting the Protection Level of Packages" . Depending on the ProtectionLevel setting, the package may not be saving the password for the database, or it may require it to be run under your user account to decrypt the password.
    • Try to create Credentials and Proxy to run the package in Jobs. Check SQL Credentials and Proxy for Agent Job.

    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



    Tuesday, September 17, 2019 7:17 AM
  • Hi Visakh, 

    Thanks for replying ,

    The job is running under sql agent service account which has sysadmin role set . Do u suggest to setup proxy account over this ?

    Santhosh


    SANTHOSH KUMAR

    Tuesday, September 17, 2019 11:44 AM
  • Hi SANTHOSH,

    You could try to create Credentials and Proxy. And have you tried my other methods? 

    Would you please kindly give us some feedback?

    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, September 18, 2019 2:24 AM