none
SSIS Error

    Question

  • Friends,

    I am getting this Error While running SSIS job in SQL Server Agent.I understand this is becoz of Connection.But Please help me how to fix

     

     

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
    04/30/2012 07:37:37,Reverse Migration,Error,0,PVUKTSTQISDB01,Reverse Migration,(Job outcome),,The job failed.  The Job was invoked by User UKROI\XE25.  The last step to run was step 1 (Migrate Data From QIS to MS Access).,00:00:02,0,0,,,,0
    04/30/2012 07:37:37,Reverse Migration,Error,1,PVUKTSTQISDB01,Reverse Migration,Migrate Data From QIS to MS Access,,Executed as user: UKROI\SVC_MSSQL. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  07:37:37  Error: 2012-04-30 07:37:38.24     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2012-04-30 07:37:39.29     Code: 0xC0202009     Source: Package Connection manager "172.21.156.119.QISDataModel"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.  End Error  Error: 2012-04-30 07:37:39.29     Code: 0xC020801C     Source: Market Review QB Market Review QB Tests [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "172.21.156.119.QISDataModel" 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: 2012-04-30 07:37:39.29     Code: 0xC0047017     Source: Market Review QB SSIS.Pipeline     Description: component "Market Review QB Tests" (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2012-04-30 07:37:39.29     Code: 0xC004700C     Source: Market Review QB SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-04-30 07:37:39.29     Code: 0xC0024107     Source: Market Review QB      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  07:37:37  Finished: 07:37:39  Elapsed:  2.012 seconds.  The package execution failed.  The step failed.,00:00:02,0,0,,,,0

    Monday, April 30, 2012 9:05 AM

All replies

  • How did you deployed package?

    what did you set for ProtectionLevel?

    did you used connection with PASSWORDs in your package?


    http://www.rad.pasfu.com

    • Marked as answer by Prabhuvincent Wednesday, May 02, 2012 9:39 AM
    • Unmarked as answer by Prabhuvincent Wednesday, May 02, 2012 9:39 AM
    Monday, April 30, 2012 9:11 AM
    Moderator
  • Hi Prabhuvincent,

    Is seems like a permission issue. The default setting for the SSIS package ProtectionLevel property is EncryptSensitiveWithUserKey. When the package is saved, SSIS encrypts only the parts of the package that contain properties that are marked "sensitive," such as passwords, usernames, and connection strings. Therefore, when the package is reloaded, the current user must satisfy the encryption requirements for the sensitive properties to be decrypted. However, the current user does not have to satisfy the encryption requirements to load the package. When you run the package through a SQL Server Agent job step, the default account is the SQL Server Agent Service account. This default account is most likely a different user than the package author. Therefore, the SQL Server Agent job step can load and start to run the job step, but the package fails because it cannot complete a connection.

    You can change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password. or you can use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file.

    For more information about it, please refer to:
    http://support.microsoft.com/kb/918760

    Thanks,
    Eileen

    Friday, May 04, 2012 8:48 AM
  • Hello,

    Can you please provide the steps you used for deploying a package. are you using SQL job for package deployment?

    Friday, May 04, 2012 9:12 AM