I have a dtsx package that fails to run. When I turn on logging I get:

The AcquireConnection method call to the connection manager "MY_DSN" failed with error code 0xC0202009.

MY_DSN is a legitimate DSN and my user has access to the database.

I don't see any messages in the SQL Profiler. I am tracing for the following:

  • Audit Login
  • Audit Logout
  • Existing sessions
  • PreConnect Complete
  • PreConnect Started.

What other diagnostic tools should I be using to be getting more useful error messages?

  • Any chance this is a 64 bit box and the DSN is in the 32 bit space (or vice versa)? Try running the package in 32 bit mode? If running from the agent, there is a check box. Otherwise, the dtexec should be in \program files (x86)\microsoft sql server\XXX\DTS\binn\ where XXX is your version. – billinkc Sep 20 '12 at 15:54
  • @billinkc Hrm there are DTSX packages calling other DTSX packages. The outer packages are definitely 64 bit. Is it possible a 64 bit dtsx package is calling a 32 bit package? – Justin Dearing Sep 20 '12 at 16:01
  • I would have to check. I assume the 32/64 bittyness of the parent package drives the child package. You can override this by running the package from the Execute Process task instead of Execute Package but that would break any variable inheritance you have coded. – billinkc Sep 20 '12 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.