none
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009 RRS feed

  • Question

  • I have deployed my packages into Sql Server and I am using Configuration File. As my Data Source is Excel, I have changed the connection string during deployment with Server Path. But I am getting the following errors. Actually the File Exist in Path. May I know What is cause of the issue? Do I need to give any permission to execute the package.

     

    SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel 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. 

     

    component "Excel Source Service Contract Upload" (1) failed validation and returned error code 0xC020801C. 

    One or more component failed validation. 

     

    There were errors during task validation. 

    DTS_E_OLEDBERROR, Error Code: 0x80004005 Source: "MS JET DB Engine" Description : Path is not valid


     

    Tuesday, March 11, 2008 4:33 PM

Answers

All replies

  •  

    How are you running the package?

     

    Check that the user running the package has access to the specified path...

    Tuesday, March 11, 2008 4:45 PM
    Moderator
  •  

    I am running through Execute Package Utility in SQL Server Management Studio, MSDB Database. Even I have added user to the Directroy where the excel file resides.
    Tuesday, March 11, 2008 5:01 PM
  •  Amzu wrote:

     

    I am running through Execute Package Utility in SQL Server Management Studio, MSDB Database. Even I have added user to the Directroy where the excel file resides.

     

    My SQL Server is 64 bit Windows 2003 Server Xeon Machine. Is it a problem running the package?

    Tuesday, March 11, 2008 6:12 PM
  • In this case, yes it is...

     

    There is no 64 bit driver for excel. The workaround is to run the package using the 32 bit version of the execution utility.

     

    http://technet.microsoft.com/en-us/library/ms141766.aspx

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1

     

    Tuesday, March 11, 2008 6:17 PM
    Moderator
  •  

    In the Project Properties->Degugging Section,  I set Run64bit RunTime to False. It started working now. Thanks for the answers
    Tuesday, March 11, 2008 10:53 PM
  • Thank You So much for this solution

     

    Thursday, March 13, 2008 9:49 AM
  • Thanks a lot for this solution!! I struggled with this for the last couple of days!

    Regards

     

    Tuesday, November 4, 2008 11:28 AM
  • Great! Thanks! I had the same problem, and your solution of setting the 64-bit runtime to false is perfect. I was so discouraged last night, spending an hour or two on this. I could get it to export to a text file, but not Excel. Thanks!

     

    Friday, December 5, 2008 4:03 AM
  • Thanks for the answer!:)
    I was trying to migrate the same package from a 32bit to a 64 bit server and was very puzzled that the SSIS import excel package failed with errors..It was indeed very helpful!!
    Wednesday, January 7, 2009 3:38 AM
  • I noticed too that the Microsoft.JET.OLEDB.4.0 driver will try to read the temp directory underneath the profile of the logged in user.

    This will cause problems if going through a SQL Agent proxy that does not have permissions to access this folder.

    If you log onto the box as admin and try to manually start the job from Agent, the job will fail with DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER- even though the same job contains file system tasks that operate correctly on the same file and folder.

    I ran sysinternals procmon to verify this: procmon shows the file system tasks correctly accessing the same file that the excel driver is pointed to.

    Procmon also shows an attempt to access the admin's profile temp directory, followed by "Access denied", and quickly followed by a load of an rll file to display the error dts message, and subsequently the threads are terminated and the process ends.

    Because of this, the process never gets to the point where it is enumerating the ISAM reg keys (HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines), nor does it ever attempt to open msexcl40.dll.

    This is a bug that needs to be addressed. (win2k3 server - 32 bit)

    To confirm this, I took the same process and scheduled it under SQL Agent for 1 minute in the future. 

    It worked perfectly.

    • Proposed as answer by appootan Wednesday, July 11, 2018 7:32 PM
    Thursday, April 16, 2009 5:53 PM
  • Hi Rafel,

    Even i have the same situation, but here i need to run the package using "dtexec" command.
    do you know how to run the package using dtexec command in this scenario ? 

    Thanks,
    Rahul

    rahulv
    Friday, May 8, 2009 4:28 PM
  •   Thanks a lot

      It is very helpful for me....
    Tuesday, May 12, 2009 9:01 AM
  • Is It possible to call the SSIS package (running 32 bit) from a web application running in 64 bit environment??
    Friday, June 12, 2009 8:47 AM
  • There are several ways to "trigger" execution of an SSIS package.  Here are some: http://code.msdn.microsoft.com/dtexecRemote
    Todd McDermid's Blog
    Friday, June 12, 2009 3:23 PM
    Moderator
  •  

    In the Project Properties->Degugging Section,  I set Run64bit RunTime to False. It started working now. Thanks for the answers

    I know it's too far ago, and I don´t know who you are.. but you saved my life!!!!  hehe   thanks a lot!  =)
    Thursday, October 15, 2009 5:38 PM
  • Wow, you guys are awesome man. Thank you so much for saving my life. Cheers :)
    Thursday, October 21, 2010 9:01 PM
  • Thank you! That solved my problem.
    Tuesday, October 26, 2010 9:10 PM
  • Thanks, Rafael. I spent few hours before following your suggestion. Still you saved me few  more hours of time. Thanks, once again.

    Monday, November 1, 2010 1:47 PM
  • @wade_b

    I had the exact same problem.  We run our SQL Agents using a lower level domain account and run our SSIS packages using a Proxy Account.  You are correct as Procmon confirmed it for me as well.  I gave the Proxy Account rights to the profile's temp directory (C:\Documents and Settings\SQLAgentDomainAccount\Local Settings\Temp) and it worked!

    Thanks!

    • Proposed as answer by Brad Deem 2 Friday, November 12, 2010 4:49 PM
    Friday, November 12, 2010 4:44 PM
  • In the Project Properties->Degugging Section,  I set Run64bit RunTime to False. It started working now. Thanks for the answers

    Great!!! Solved the problem.


    rbj

    Wednesday, December 8, 2010 7:07 PM
  • Thank you very much this is what I was looking for
    Monday, October 24, 2011 10:09 AM
  • It works to me.  Thank you!

    :)

    Wednesday, September 5, 2012 3:50 PM
  • Hello rafael,

    I have the same error reports but unfortunately I can't change the value of the Debug Option Run64Runtime to "False" or to be precise the value is anyway set on "False" and fix. I don't use a configuration file, does it make a difference?

    I am thankful for any help.

    Regards Cetin

    Monday, December 9, 2013 8:54 AM
  • Thanks, this works :)

    Tuesday, April 22, 2014 9:35 PM
  • Thanks you very much. I was very useful :)
    Monday, November 24, 2014 4:24 PM
  • Awesome
    Wednesday, August 10, 2016 6:37 AM
  • thanks!!!!!!this worked for me....I think this is the solution for running the 32 bit Access DB.
    Thursday, June 15, 2017 10:28 AM
  • Thanks a ton, I did the same as suggested and it was due to the proxy account not having privileges to the temp folder for the JET excel library to run, as i found out from procmon. Gave privileges and it started working.

    Wednesday, July 11, 2018 7:34 PM
  • Thank you very much, you helped me a lot
    Thursday, December 13, 2018 9:23 AM
  • it worked for me, you really saved my life :)

    Thanks

    Ashutosh kumar

    Thursday, September 26, 2019 10:16 AM
  • My only concern is that, does it have any side affect..?
    Thursday, September 26, 2019 10:18 AM
  • I know this is an extremely old thread, but here I am in 2019 with the same issue ELEVEN YEARS LATER and this worked, but I do want to mention that I had to restart visual studio after making the change for it to work.  I thought initially it wasn't going to work.  Thanks!!
    • Proposed as answer by TomK21 Thursday, October 24, 2019 11:19 PM
    Tuesday, October 15, 2019 4:01 PM
  • 2019 here as well ... and only days later! Still with this issue! THANK YOU for mentioning to restart VS.  I have been working with this forever and thought I had found the answer here only for it not to work ... until I saw your suggestion.  SSIS runs fine from Visual Studio, but when I deploy the package to our SQL Server, it bombs.  We don't have Microsoft.ACE.OLEDB.15 installed/configured, nor are we able/allowed to do so!  My only choice was to change the Excel version in the connection manager to 2007-2010 ... except, when I did this, not only was it still "broken" on the server, but not running in Visual Studio would get the error we all came to this thread about.  This is a perfect solution and I don't have to ask my users to downgrade their Excel output.  THANK YOU!!
    Thursday, October 24, 2019 11:34 PM