Fix it now
A data flow component asked its connection manager for a connection at run time and did not get one. The published message is DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER, and it always carries an inner error code – that inner code, not this one, is the diagnosis.
dtexec /File "D:\Packages\Load.dtsx" /Reporting EWCDI
- Read the whole stack, not the first line. In Microsoft’s own worked example the innermost provider error is raised first, 0xC020801C follows it, and 0xC0047017 wraps both – so read upward.
- If 0xC0016016 appears, the package could not decrypt its own protected XML node. That is the protection level and it is by far the most common cause.
- Check the package’s ProtectionLevel in Visual Studio.
EncryptSensitiveWithUserKeyties the stored password to the profile of whoever saved it, and nobody else can read it. - For anything running unattended, set ProtectionLevel to
DontSaveSensitiveand supply the password at run time from a parameter, a configuration or a catalogue environment. - If 0xC001000E appears instead, a connection manager the package refers to does not exist at run time – a configuration or parameter that should have created it did not apply.
Changing the protection level does not re-enter the password for you. Open each connection manager afterwards, type it again and save.
If the package now runs under the account that was failing, you are done. If the inner code is a provider error rather than a decryption one, the next section separates credentials from configuration.
Why it happens
Sensitive values in a package – principally passwords – are never stored in clear text. Under EncryptSensitiveWithUserKey, which is the default, they are encrypted with a key derived from the profile of the user who saved the package. That works perfectly while you are the one pressing the button. The moment anything else runs it, the key is unavailable, the protected XML node cannot be decrypted, the connection manager is handed an empty password, and the data flow reports that it could not acquire the connection.
Microsoft’s description of the same option says it plainly: only the same user, using the same profile, can load the package; if a different user opens it, the sensitive information is replaced with blanks. That is why the failure looks environmental. Copying the file to another machine, running it under an Agent service account, or executing it through a proxy all change which profile is in play, and nothing about the package’s contents has changed at all.
The project deployment model removes this class of failure rather than working around it. The SSISDB catalogue uses the ServerStorage protection level: when you deploy a project to the Integration Services server, the catalogue encrypts the package data and sensitive values itself and decrypts them when they are retrieved, with environments supplying the values at run time. There is one trap worth knowing – if you export the project back out to the file system as an .ispac, the protection level is automatically changed to EncryptSensitiveWithUserKey, and you are back where you started.
The protection level hides the password from the runtime account
You have this one if 0xC0016016 accompanies the failure, and the package runs perfectly when you execute it yourself in Visual Studio.
- Set ProtectionLevel to
DontSaveSensitiveand supply the password from a parameter, an environment or a configuration at run time. This is the cleanest option. - If the password must live in the file, use
EncryptSensitiveWithPasswordand supply that password to whatever executes the package. - Better, deploy the project to the Integration Services catalogue and hold sensitive values in an environment.
- Re-enter every password after the change, because changing the level cannot restore values it could not read.
The account running the package has no rights on the target
You have this one if The inner record is a provider error naming a login, and the same connection works under your own account.
- Identify the account that actually runs the package: the Agent service account, or the credential behind the proxy if the job step uses one.
- Grant that account the permissions it needs on the database, share or file system it is reaching for.
- For Windows authentication against a remote server, remember the package connects as the runtime account and not as you.
- Where the package connects onward from one server to a second using Windows authentication, this is a delegation problem rather than a permissions one and needs service principal names and constrained delegation configured.
A configuration or parameter did not apply
You have this one if 0xC001000E, or a connection string that is obviously the design-time one rather than the production one.
- Check that the configuration file or environment reference the job step points at exists and is readable by the runtime account.
- For catalogue deployments, confirm the execution is using the environment you think it is, and that every sensitive parameter has a value bound to it.
- Log the effective connection string at run time so you can see what the package received rather than what you intended it to receive.
Microsoft’s own troubleshooting example for this error is a configuration file that supplied an empty property the provider did not recognise. The connection string was present and the connection still failed.
Validation fails before the package does any work
You have this one if The failure happens during validation, and the object the component points at is created by an earlier task in the same run.
- Set
DelayValidationto true on the task or container whose objects are created earlier in the same execution. - Set
ValidateExternalMetadatato false on the individual data flow components that reference run-time objects. - Use both deliberately and sparingly – they postpone genuine errors to run time, which makes other faults harder to find.
Full reference
Reading the error stack in the right direction
Error: 0xC0202009 at Package, Connection manager "OLEDB Provider":
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components"
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors..."
Error: 0xC020801C at Data Flow Task, Oracle OLEDB Source [1]:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER...
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline:
component "Oracle OLEDB Source" (1) failed validation and returned error code 0xC020801C.
That is the shape of a real failure, taken from Microsoft’s own troubleshooting article. The last line is the one people paste into a search box, and it is the least informative of the four. The first line is the diagnosis. When someone tells you 0xC020801C, the next question is always “what was above it in the log”.
The codes in this group
| Code | Symbolic name | What it says |
|---|---|---|
| 0xC020801C | DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER | The AcquireConnection call to a named connection manager failed with an inner code |
| 0xC0016016 | DTS_E_UNPROTECTXMLFAILED | Failed to decrypt a protected XML node – the sensitive value in the package |
| 0xC0202009 | DTS_E_OLEDBERROR | An OLE DB error occurred, with the provider’s code in the message |
| 0xC001000E | DTS_E_CONNECTIONNOTFOUND | The named connection was not found in the connections collection |
| 0x80040E4D | Not published by Microsoft | An OLE DB provider HRESULT that appears under DTS_E_OLEDBERROR; read the provider’s own Description line |
Protection levels, and which to choose
| ProtectionLevel | What Microsoft says it does | Use it when |
|---|---|---|
DontSaveSensitive |
Suppresses sensitive values on save; another user gets blanks and must supply them | Anything unattended. This is the answer in most cases |
EncryptSensitiveWithPassword |
Encrypts only sensitive values, with a password supplied at creation or export | The password must travel with the file and you can supply it at run time |
EncryptSensitiveWithUserKey |
Encrypts sensitive values with the current user profile; only that user can load them | Local development only. This is the default, and the cause of this error |
EncryptAllWithPassword |
Encrypts the whole package with a password | The package body itself is sensitive |
ServerStorage |
Protects the package using database roles; the SSISDB catalogue uses this | Project deployment to the catalogue |
What the catalogue changes
- Sensitive values are encrypted by the catalogue rather than by a user profile, and decrypted when retrieved.
- Environment references supply per-environment values without editing the package.
- Execution reports and the event message views record every validation and run-time message with the component that produced it.
- Exporting a project back to the file system silently changes its protection level to
EncryptSensitiveWithUserKey, so an .ispac pulled off the server and run from a share reintroduces the original problem.
When it is not credentials at all
Two failures wear the same message. A provider error saying the server was not found is a name resolution, firewall or instance name problem, and no protection level will change it – test the same connection string with a UDL file or a small script from the machine that runs the package. A 64-bit process failing to load a provider that only exists in 32-bit produces its own error further down the stack, and belongs with the Agent job step article rather than this one.
Proving the fix rather than assuming it
- Run the package from the account and by the mechanism that failed – not from your desktop.
- Confirm each connection manager reports a successful acquisition in the execution messages.
- Confirm the destination received the expected row count.
- In a test environment, change the runtime account’s password once and re-run. If the package still works, no credential is baked into the file.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
0xC020801C |
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER: the AcquireConnection call to the named connection manager failed with the inner error code shown | Microsoft Learn |
0xC0016016 |
DTS_E_UNPROTECTXMLFAILED: failed to decrypt a protected XML node, which is how a stored password is held | Microsoft Learn |
0x80040E4D |
An OLE DB provider HRESULT seen beneath DTS_E_OLEDBERROR. Read the provider’s own Description line in the OLE DB record rather than the number | not published by the vendor |
0xC001000E |
DTS_E_CONNECTIONNOTFOUND: the named connection was not found in the connections collection at run time | Microsoft Learn |
Confirm the fix worked
- The package runs from the same account and by the same mechanism that failed.
- Every connection manager reports a successful acquisition in the execution messages.
- The destination row count matches what the source supplied.
- Changing the runtime account’s password in a test environment does not break the package, which proves no credential is stored in the file.
- No 0xC0016016 appears anywhere in the run log.
Questions people ask about this
Which protection level should I use?
For anything running unattended, DontSaveSensitive with values supplied at run time from a catalogue environment or a configuration. It keeps passwords out of the package file and removes the whole class of decryption failures.
Why does it work in Visual Studio and fail as a job?
Because Visual Studio runs it as you and the Agent runs it as a service account or a proxy. Under the default protection level the stored password is readable by the first and not by the second, and Microsoft documents that the other user simply gets blanks.
Do I need to buy anything to fix this?
No. Integration Services is part of the SQL Server installation you already have, and this is a configuration problem inside a package you already own.
How do I see more detail than the error text?
For catalogue deployments, use the execution reports and the event message views, which record every validation and run-time message with its component. For file-based execution, turn on package logging or capture the output of dtexec.
I exported the project from the catalogue and now it fails again. Why?
Because exporting an .ispac to the file system changes the protection level to EncryptSensitiveWithUserKey. The values that the catalogue was holding are now tied to whoever did the export.
