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

  • Question

  • I am using BIDS2005 and SQL server 2005 to extract the data from different sheets of the same excel worksheet by following the tutorial given below:

    http://danajaatcse.wordpress.com/2010/05/05/extracting-data-from-multiple-sheets-in-an-excel-file-in-ssis/

    I have followed all the steps as it is..DelayValidation is set to true..Run64bit is set to false..when I preview the data in Excel source..SSIS is showing the data properly..but when I try to run the package I am still getting this error:

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

    Can somebody please help in resolving this issue? Thanks in advance..

    p.s. I cannot use windows update to update anything as its an office laptop.

    Tuesday, July 17, 2012 7:17 PM

Answers

All replies

  • Hi,

    In your Project Properties, Debugging Section, set the Run64bit RunTime to False and try again.

    Regards,

    Vinaya


    Tuesday, July 17, 2012 10:06 PM
  • Thanks for replying..but as mentioned in the original thread..already tried that..still not working.. :(

    any other solutions?

    Tuesday, July 17, 2012 10:18 PM
  • It is very simple to load the first sheet of an excel file with diff names..

    Here is the steps to follow.

    1. Drag a foreach loop container in to Control flow,

    2.Create a package variable "FileName" with datatype string, and specify the value.(file location with extension.)

    3. Create another  package variables "Sheet" with datatype string specify the value ( eg:Sheet1$ or abcd$3.Edit the foreach loop container (rename to FELC1)Click on the collection ,set the enumarator type to foreach file enumarator , and provide the folder name (C:\hai\excelfiles)and file name(*.xlsx) in mentioned text boxes and check fully qualified option.

    4. Click on variable Mappings and specify the variable "FileName" and set index to "0".

    5. Now drag one more foreach loop container(rename to FELC2) with in the first one,Click on the collection ,set the enumarator type to foreach ADO.NET Rowset Schema  Enumarator , and specify the conection (create an ADO.NET Rowset Schema  Enumarator)  and select  schema as Tables from the dropdown.

    6.Click on variable Mappings and specify the variable "Sheet" and set index to "2".

    7. Now Drag an DataFlowTask(DFT) inside the foreachloop container, edit the DFT and provide an connection to source and destination.

    Imp::  Now Create a connection to your excel file and , select the Data Access Mode as "Table or View" and mention the Name of the excel sheet as Sheet1$(this allow you to read data from first sheet of each file).

    12.Now Go to Connection Manager tab and right click on Excel connection manager and Click on properties,

    On Properties Window open the toggle expresion tab and select the ConnectionString on properties and wite the Expresion as "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::FileName] +";Extended Properties="+"\""+"Excel 12.0;HDR=YES"+"\""+";"


    13 Similarly for ADO.NET Connection Manager also Click on properties, use the above expression(same)

    Close it and save it.

    14. Now execute the package :), this will fill your requirement.

    • Proposed as answer by King kalyan Wednesday, July 18, 2012 9:55 AM
    • Unproposed as answer by King kalyan Wednesday, July 18, 2012 9:56 AM
    • Proposed as answer by King kalyan Wednesday, July 18, 2012 9:56 AM
    Wednesday, July 18, 2012 4:43 AM
  • What is the file extension of your excel file. Try changing it to save the excel as 2003 format in .xls


    My Blog    |      Ask Me     |      SSIS Basics     

    Wednesday, July 18, 2012 4:56 AM
  • Hi Desi Bov,

    Please changed the DelayValidation property of your data flow task to true, for more details about the issue, please see: http://blogs.msdn.com/b/ramoji/archive/2009/03/10/the-acquireconnection-method-call-to-the-connection-manager-failed-with-error-code-0xc0202009.aspx

    Thanks,
    Eileen


    • Edited by Eileen Zhao Tuesday, July 24, 2012 9:52 AM
    • Marked as answer by Eileen Zhao Monday, August 6, 2012 6:07 AM
    Tuesday, July 24, 2012 9:51 AM