locked
SSIS (2012) package is getting failed when scheduled from Agent Job RRS feed

  • Question

  • I have an SSIS package that runs without any problems when executed through BIDS.

    However, when I schedule the SSIS as an Agent job, it fails and the following is the error in the job's errorlog. Any help to get rid of this issue ? Thanks in advance.

    Error:

    Message
    Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation.
    All rights reserved.    Started:  4:16:12 AM  Error: 2013-05-15 04:16:13.03     Code: 0xC001405F     Source: Package      

    Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct.
    Package load will attempt to continue without the encrypted information.  

    End Error  Error: 2013-05-15 04:16:13.18     Code: 0xC001405F     Source: Package      
    Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct.
    Package load will attempt to continue without the encrypted information.  End Error  Error: 2013-05-15 04:16:13.28     Code: 0xC0208449     

    Source: Data Flow Task ADO NET Source [2]     
    Description: ADO NET Source has failed to acquire the connection {F0871F2A-1AA3-4863-BEAE-457F60A0AEA0} with the following
    error message: "ERROR [IM003] Specified driver could not be loaded due to system error  1114:
    A dynamic link library (DLL) initialization routine failed.
    (Oracle in OraClient11g_home1, C:\OracleClient32\product\11.2.0\client_1\BIN\SQORA32.DLL).".  End Error  
    Error: 2013-05-15 04:16:13.28     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     

    Description: ADO NET Source failed validation and returned error code 0xC0208449.  End Error  
    Error: 2013-05-15 04:16:13.28     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     
    Description: One or more component failed validation.  End Error  
    Error: 2013-05-15 04:16:13.28     Code: 0xC0024107     Source: Data Flow Task      
    Description: There were errors during task validation.  End Error  
    DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:16:12 AM  Finished: 4:16:13 AM  Elapsed:  0.562 seconds.  
    The package execution failed.  The step failed.

    Wednesday, May 15, 2013 6:21 PM

All replies

  • You deployed the package with the protection level set to EncryptWithUserkey, but under the agent you need "Reply on server..."

    Arthur My Blog

    Wednesday, May 15, 2013 6:33 PM
  • Hi Nick,

    The issue may occur if you have set the protection level of the package to EncryptAllWithPassword or EncryptSensitiveWithPassword. To avoid this issue, please open SQL Server Management Studio (SSMS), navigate to the Steps tab of the Job Properties, an then switch to the Command line tab, and add “/DECRYPT password" parameter to the DTEXEC command line.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Rajkumar5055 Saturday, May 18, 2013 6:35 AM
    Thursday, May 16, 2013 8:17 AM
  • Arthur,

    I have difficulty in understand the solution you asked to check here. May I request you to explain it. Thanks in advance.

    Friday, May 17, 2013 7:55 AM
  • What is the package protection level set to?

    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, May 17, 2013 8:10 AM
  • Hi Nitesh,

    Thanks for addressing the issue.

    The package protection let is set to : "Encrypt sensitive data with Password"

    When we execute the package directly from the filesystem, it is working fine. When we schedule it as an Agent job, it is getting failed.

    The captured error message is from Job's error log.

    Friday, May 17, 2013 9:32 AM
  • You need to either provide the package password when you run it under the Agent, or change the protection level for it to "Rely on Server Storage..."

    Arthur My Blog

    Saturday, May 18, 2013 2:46 AM