ADO NET Source has failed to acquire the connection {----} with the following error message: "Could not create a managed connection manager."

Question

SSIS package and sql server are both on different server. By using proxy i am executed package on another server.

but it provides me error while executing through sql agent job 

ADO NET Source has failed to acquire the connection {----} with the following error message: "Could not create a managed connection manager."

Answer 1

Hi Vivek Waghela,

For this issue, perhaps you build the package with 32-bit run mode in your SSDT, so you need to set the SQL Agent job with 32 bit mode to execute the package.

Job properties->Steps->Advanced

Check if this helps.

Regards,

Pirlo Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Pirlo Zhang,

I have already selected 32 bit runtime option then too its providing same error.

Regards;

Vivek

Answer 3

Hi Viwek Waghela,

Could you make sure the version of ADO.NET driver is 32 bit on your another server?

Regards,

Pirlo Zhang 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Hi Pirlo Zhang,

The SSDT Installed is of 32 Bit and ADO driver would be also 32 Bit.

Regards,

Vivek

Answer 5

Is there any solution for this error????

Answer 6

So, you have stored your SSIS package files (.dtsx) on a separate server from the SQL Server machine?

What version/edition/service pack level of SQL Server are you running?

HTH,

---

Phil Streiff, MCDBA, MCITP, MCSA

Answer 7

Microsoft SQL Server Management Studio 14.0.17119.0
Microsoft Analysis Services Client Tools 14.0.608.142
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 6.0 
Microsoft Internet Explorer 9.11.9600.18739
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.9600

Answer 8

Hi Vivek Waghela,

So did you use proxy account to execute the SQL Agent job? 

The user that executes the package through SQL job is different than the user that runs the project in BIDS/SSDT.
Please make sure that the proxy account that executes the SQL Jobs has permissions to access all connections.

Regards,

Pirlo Zhang 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 9

The User Name is same which i am using in proxy and while executing package. and also provided permissions

Answer 10

Hi Vivek Waghela.

This is odd. Are there any other error messages? 

Check the package Execution Report and Job history.

Regards,

Pirlo Zhang 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 11

Message
Executed as user: (------------). Microsoft (R) SQL Server Execute Package Utility  Version 13.0.1601.5 for 32-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  12:38:01  Error: 2017-09-01 12:38:01.80     Code: 0xC0208449     Source: (--------------)[41]     Description: ADO NET Source has failed to acquire the connection {------------------} with the following error message: "Could not create a managed connection manager.".  End Error  Error: 2017-09-01 12:38:01.80     Code: 0xC0047017     Source: (--------) SSIS.Pipeline     Description: (----------) failed validation and returned error code 0xC0208449.  End Error  Error: 2017-09-01 12:38:01.80     Code: 0xC004700C     Source: (--------) SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2017-09-01 12:38:01.81     Code: 0xC0024107     Source: (------) Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:38:01  Finished: 12:38:01  Elapsed:  0.578 seconds.  The package execution failed.  The step failed.

Answer 12

This is error in sql job history

Answer 13

Hi Vivek Waghela,

-->>SSIS package and sql server are both on different server.

So, did you use SQL Agent Job to execute remote packages located in shared folder? Have you tried to move the package to same server and check if the issue persist.

For this error message, generally the reason is either the version of driver is not available or the account has no sufficient permission. Besides, I'd suggest you to try to use OLE DB source to check if the issue is related to the ADO.NET source itself.

Regards,

Pirlo Zhang  

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 14

We have installed a biztalk server as source is SAP. In  that same same i hav created package to pull data from SAP which is working fine.

But sql server is in different server from which i have use sql agent job with proxy to execute this package. In this i am faceing error

Answer 15

Have you tried locating any of the package files on the same server with SQL Server and running from there, to see if you get the same error?

HTH,

---

Phil Streiff, MCDBA, MCITP, MCSA

Answer 16

have you found a solution to your problem? 

I have the same problem, I can't solve it.

Answer 17

I know this is old but just want to put an answer to my fixed. Make sure the MySQL connector net driver version on your development computer matches the version install on your SQL Server that is running the package.  I upgrade my computer and reinstall the latest drivers which causes the problem for me.

Hope that help someone...