Wednesday, December 15, 2010

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

this error message has many sources:
  • connection is used with user id, and password and not saved
  • configuration file does not have the server name
  • data flow task is not marked as delayvalidation = true
  • low disk space in the server you try to get the data from
  • Oracle provider
  • Excel 64 provider


I tried to google and found most of the replies are for Oracle and Excel, and sometimes the password. while my case does not include any !!!!!

so I found the delayvalidation and it seemed works but nothing happened. I tried to make the things easier as My package runs in 4 hours and deals with more than 30Million * 200,000 * 1000 records with other tables that compared with the above is look-ups.

what make me confused that it sometimes runs ok, but some other times gives me an error at anytime!!!!

Now, I figured that it may be due to disk space.
Enhanced by Zemanta

2 comments:

  1. Hey Ahmed,

    Could you please let me know how you arrived at the conclusion that this may be due to disk space ? I am facing a similar issue since a couple of months.

    ReplyDelete
  2. Hi Kiran,

    Trial and error, when the Tempdb is taking the whole space, the SQL server cannot open a new connection. So, when shrinking the tempdb it goes fine.

    ReplyDelete