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

  • 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."

    Thursday, August 31, 2017 7:11 AM

All replies

  • 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 MSDNFSF@microsoft.com.

    Thursday, August 31, 2017 8:51 AM
    Moderator
  • Hi Pirlo Zhang,

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

    Regards;

    Vivek

    Thursday, August 31, 2017 9:01 AM
  • 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 MSDNFSF@microsoft.com.

    Thursday, August 31, 2017 9:03 AM
    Moderator
  • Hi Pirlo Zhang,

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

    Regards,

    Vivek

    Thursday, August 31, 2017 10:26 AM
  • Is there any solution for this error????

    Thursday, August 31, 2017 11:32 AM
  • 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

    • Edited by philfactor Thursday, August 31, 2017 4:21 PM
    Thursday, August 31, 2017 12:08 PM
  • 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

    Friday, September 1, 2017 5:57 AM
  • 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 MSDNFSF@microsoft.com.

    Friday, September 1, 2017 6:20 AM
    Moderator
  • The User Name is same which i am using in proxy and while executing package. and also provided permissions

    Friday, September 1, 2017 6:59 AM
  • 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 MSDNFSF@microsoft.com.

    Friday, September 1, 2017 7:04 AM
    Moderator
  • 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.
    Friday, September 1, 2017 7:31 AM
  • This is error in sql job history
    Friday, September 1, 2017 7:32 AM
  • 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 MSDNFSF@microsoft.com.

    Friday, September 1, 2017 7:56 AM
    Moderator
  • 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

    Friday, September 1, 2017 9:21 AM
  • 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

    Friday, September 1, 2017 12:24 PM
  • have you found a solution to your problem? 

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


    • Edited by Ali Wahmane Sunday, October 28, 2018 8:32 PM
    Sunday, October 28, 2018 8:31 PM
  • 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...

    Tuesday, October 15, 2019 2:29 PM