March 9, 2010 at 7:33 pm
I have many packages that run just dandy using the same logins/proxys/credentials/roles as this one, but alas, I can't get past the dreaded rookie error. Of course it runs fine from BIDS, and it runs fine from SSMS/IS, sure won't run as a job though. I've tried using "administrator" and "sa" EVERYWHERE just to try and get it going. Contrary to the error message, I DO know my sa password!
After two days of this, it might be time to head back to C# with my tail between my legs.
:blush:
Date3/9/2010 7:20:54 PM
LogJob History (bbb)
Step ID1
ServerCSWEB\CSSQL2K8
Job Namebbb
Step Namebbb
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: CSWEB\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:20:54 PM Error: 2010-03-09 19:20:55.57 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". 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: 2010-03-09 19:20:55.64 Code: 0xC0202009 Source: AwardsBuildFile Connection manager "cnDailyData" 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 'sa'.". End Error Error: 2010-03-09 19:20:55.64 Code: 0xC020801C Source: DF Hyatt Awards Export CSSql - HyattAwardsExport Sproc [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "cnDailyData" 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: 2010-03-09 19:20:55.64 Code: 0xC0047017 Source: DF Hyatt Awards Export SSIS.Pipeline Description: component "CSSql - HyattAwardsExport Sproc" (1) failed validation and returned error code 0xC020801C. End Error Error: 2010-03-09 19:20:55.64 Code: 0xC004700C Source: DF Hyatt Awards Export SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2010-03-09 19:20:55.64 Code: 0xC0024107 Source: DF Hyatt Awards Export Description: There were errors during task validation. End Error Error: 2010-03-09 19:20:55.64 Code: 0xC00220DE Source: Exec CreateAwardsFile Description: Error 0xC0012050 while loading package file "D:\SSIS\ISCS\AwardsBuildFile.dtsx". Package failed validation from the ExecutePackage task. The package cannot run. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:20:54 PM Finished: 7:20:55 PM Elapsed: 0.765 seconds. The package execution failed. The step failed.
.
March 9, 2010 at 7:48 pm
There is protected data stored in the package and the package protection level was probably set at EncryptSensitiveWithUserKey meaning that the SSIS has to be run as the last user who edited the package. Protected data is usually like login credentials for a connection.
CEWII
March 9, 2010 at 7:58 pm
Right you are, wow, thanks!! That makes perfect sense. I changed the credential my proxy points to the owner/editor of the package and I'm in business!
SA does no good in this situation.
.
March 9, 2010 at 8:07 pm
Since I almost exclusively use trusted connectivity I often set the package protection level to DontSaveSensitive. I use package configurations to set protected information or adjust the sql agent job to change it.
CEWII
March 9, 2010 at 8:13 pm
Using that technique, do you embed the password in the connect string when you define the variable?
.
March 9, 2010 at 8:18 pm
As a policy I always try to use trusted connections. When I can't I set the credentials either through a package configuration or adjust them in the SQL agent job. If you were to get the source code of my package it would not tell you any passwords.
CEWII
March 9, 2010 at 8:44 pm
I've had my share of frustrations with trying to define vars at runtime. I always seem to end up with this error one way or another:
Date3/9/2010 8:46:20 PM
LogJob History (AwardsExport)
Step ID1
ServerCSWEB\CSSQL2K8
Job NameAwardsExport
Step NameExec AwardsExport Package
Duration00:00:00
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: CSWEB\BSavoie. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. The argument "\AwardsExport.Variables[User::sOutputDir].Value;\\CSSql\FTPOut\HyattAwards" /REPORTING E " has mismatched quotes. The command line parameters are invalid. The step failed.
.
March 9, 2010 at 9:43 pm
If a package executes another package, does the child package run with the same credentials as the parent?
.
March 10, 2010 at 4:37 am
Yes, I'm not aware of a way to change it. Even accidently..
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply