When you create a DTSX the "Security/Protection Level" property will be set by default to "EncryptSensitiveWithUserKey". Why should you want to know this?
Well, this property means it will encrypt sensitive information with your user credentials. Another thing that is not so explicit about this setting is that all sensitive information will be bound to your machine. Now, this might not be a good idea since most of the time you will not develop in production environment.
In practical terms this means whenever you try to edit or run this dtsx in another machine errors will be raised when you try to read the credentials for the connection to the database.
Error loading mydtsx.dtsx: 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.
Validation error. Data Flow mydtsx: Connection1 [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
One of the ways to overcome this is to change the property to EncryptSensitiveWithPassword. This way you will be asked to set a password, that will be required to decrypt sensitive information when you run/edit it next time.
e.g. DTEXEC /FILE "C:\MyDTSX.dtsx" /MAXCONCURRENT "-1" /CHECKPOINTING OFF /REPORTING EW /DE MyPassword
TIP: You can set this commad line to run in the scheduled tasks. To set it to run as system account use: NT Authority\System
No comments:
Post a Comment