SSIS from Stored Proc returns error
Team - I have an SSIS package. When I run the SSIS, it runs perfectly however when I call the package from stored proc, i get the below error. Pls help.
Error Output
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started: 11:48:19 PM
Error: 2013-05-10 23:48:19.90
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: 2013-05-10 23:48:20.19
Code: 0xC0202009
Source: Package Connection manager "xxxxxxxx"
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 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'xxxxxxx'.".
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC020801C
Source: Data Flow Task OLE DB Destination [233]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
is with more information on why the AcquireConnection method call failed.
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC0047017
Source: Data Flow Task SSIS.Pipeline
Description: component "OLE DB Destination" (233) failed validation and returned error code 0xC020801C.
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC004700C
Source: Data Flow Task SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:48:19 PM
Finished: 11:48:20 PM
Elapsed: 0.344 seconds
NULL
Error Output
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started: 11:48:19 PM
Error: 2013-05-10 23:48:19.90
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: 2013-05-10 23:48:20.19
Code: 0xC0202009
Source: Package Connection manager "xxxxxxxx"
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 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'xxxxxxx'.".
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC020801C
Source: Data Flow Task OLE DB Destination [233]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
is with more information on why the AcquireConnection method call failed.
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC0047017
Source: Data Flow Task SSIS.Pipeline
Description: component "OLE DB Destination" (233) failed validation and returned error code 0xC020801C.
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC004700C
Source: Data Flow Task SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2013-05-10 23:48:20.19
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:48:19 PM
Finished: 11:48:20 PM
Elapsed: 0.344 seconds
NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Dan7el provided a light for the solution.
When I run the SSIS, it runs perfectly
Here you are running it as yourself.
however when I call the package from stored proc, i get the below error.
And here you are executing it with the account for the SQL Server service.