none
Error: ADO NET Destination has failed to acquire the connection. The connection may have been corrupted. RRS feed

  • Question

  • I am creating a SSIS Package that is transferring data from Oracle 9i table to Sql Server 2005 table. 

    I am developing this package from my local machine and accessing Oracle and Sql Server on the network.  I am using ADO.NET connections and ADO.NET Source and Destination.  It appears that using ADO.NET will allow me to workaround the restriction of remote development when using OLEDB.

    I have dbo owner permissions on the SQL Server database and schema. 

    Upon creating both connections, they were tested with success.  Strangely, when I run the package, I connect to Oracle fine, but the package fails when attempting to connect to SQL Server.  I get the following message.:

    [ADO NET Destination [122]] Error: ADO NET Destination has failed to acquire the connection {277EFBD9-5AB9-4096-878F-DD842718A046}. The connection may have been corrupted.

    Manually the connection is still testing successfully.  I have recreated it a few times.

    Any ideas what would be causing this?

    Thanks,
    Mark


    Thursday, February 18, 2010 11:49 PM

All replies

  • +1 trying to transfer from Mysql to SQL Server 2008.
    Tuesday, March 16, 2010 8:13 PM
  • It's been a while since I posted this question.  So I do not remember the exact cause of the problem. But, make sure that you are using the Microsoft OleDB For Oracle which is available in the ADO.NET Source Dialog.
    Tuesday, March 16, 2010 10:07 PM
  • I had the same problem but with mySQL server connection. It would run successfully from SSIS Package Execute Utility but would fail to acquire connection when I tried to run from SQL Server Agent job.

    In the end the solution for me was to create a System Dsn connection string in ODBC services instead of User Dsn. Once I created System Dsn connection string with the identical name, SQL Server Agent job ran successfully.

    Friday, June 18, 2010 4:27 PM