none
SSIS 2012: SSIS Error Code "Excel Connection Manager" failed with error code 0xC0202009.

    Question

  • Hi,

    This is kind of weird issue that I am experiencing with excel connection manager in SSIS 2012. This issue occurs sometimes but when I close and re-open SSDT (SQL Server Data Tools - newer BIDS) then this issues goes off temporarily.

    Just FYI, through SSDT environment I executed the package successfully with both settings Run64bit runtime setting to Yes and No when error does not occur.

    So far I have installed http://www.microsoft.com/en-us/download/details.aspx?id=13255 (Microsoft Access Database Engine 2010 Redistributable).

    I still remember for older versions of SQL Server (2005 & 2008) that I have executed Excel connection SSIS packages with Run64bit runtime = false i.e. in 32-bit mode. As far as I know I think Excel 64-bit issue has been resolved with SQL Server 2012 release.

    *****************************************************************

    Here is the detail of error message:

    TITLE: Package Validation Error
    ------------------------------
    Package Validation Error
    ------------------------------
    ADDITIONAL INFORMATION:
    Error at Data Flow Task [Excel Source [2]]: 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.

    Error at Data Flow Task [SSIS.Pipeline]: Excel Source failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    Error at Package [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "Unspecified error".

     (Microsoft.DataTransformationServices.VsIntegration)
    ****************************************************************

    Here are my environment details:

    SQL Server 2012 {Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) } , Excel 2010 (32-bit). I am developing SSIS code on Virtual desktops with Windows 7 32-bit OS.

    Also it occurred to me that since Virtual Desktops are on Shared Infrastructure, the source files and SSIS packages (code) can be on Shared drives for e.g. \\<Corpnet>\userdata\<Corp_Users_Grp>\<Username>\Visual Studio 2010\Projects\Integration Services Project2\Integration Services Project2\Package.dtsx.

    Does this kind of Shared drives have any impact to give this issue?

    Thanks in advance!

    Ketan

    P.S.: I had look at this forum question --> http://social.msdn.microsoft.com/Forums/sqlserver/en-US/903bbe1d-e070-4c43-9d3b-0a5193550029/64bit-error-in-excel-connection-manager-in-ssis

    Tuesday, August 13, 2013 11:25 PM

Answers

  • Hi Ketan,

    The issue with bitness did not go away in SSDT because it is still a 32 Bit application.

    The Excel driver you used is also a 32 bit one, so there is no need to emulate a 64 bit system, indeed even in production the package needs to run under the 32 bit mode.

    The error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER is generic. Means the package cannot open a connection.

    Reasons range from security to drivers.

    In your case it may indeed be the issue with remote paths, e.g. when the Excel file is in a share.

    The remedy in this case is in not using mapped drives, but rather a UNC path notation.


    Arthur My Blog

    Wednesday, August 14, 2013 3:13 PM
  • Unfortunately Excel was not meant to be the data transfer media. It is a desktop UI centric application which ended up on every machine and now SSIS needs to embrace this challenge. Of course user input in an incorrect format may break any package.

    Re #2, I see why not, doable with the proper SSIS expression.

    3: You can deal with nulls using SSIS expressions. But you need to assign proper datatypes, the issue mostly has to do with the shallow data sampling by the Excel Driver.

    4: The Excel connector does see all the tabs (unless they have special chars as #,$ etc.)

    CSV is more manageable, ETL solutions involving CSV files are cheaper.

    Most issues though are very well covered by now and it seems that you can find an answer to every nuance.

    PS: Todd McDermid has several good posts that cover many gotchas using Excel in SSIS http://toddmcdermid.blogspot.ca/


    Arthur My Blog

    • Marked as answer by Ketan Patil Friday, August 16, 2013 11:46 AM
    Thursday, August 15, 2013 3:00 PM

All replies

  • Hi Ketan,

    The issue with bitness did not go away in SSDT because it is still a 32 Bit application.

    The Excel driver you used is also a 32 bit one, so there is no need to emulate a 64 bit system, indeed even in production the package needs to run under the 32 bit mode.

    The error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER is generic. Means the package cannot open a connection.

    Reasons range from security to drivers.

    In your case it may indeed be the issue with remote paths, e.g. when the Excel file is in a share.

    The remedy in this case is in not using mapped drives, but rather a UNC path notation.


    Arthur My Blog

    Wednesday, August 14, 2013 3:13 PM
  • Hi,

    Check whether you have permission on the remote server drive to read the files.

    Set delayvalidation = true as u wont be having the drive path before mapping the shared drive/ folder.

    Check the driver version you are using -

    Development (32-bit)
    Provider=Microsoft.Jet.OLEDB.4.0; 

    Production (64-bit)
    Provider=Microsoft.ACE.OLEDB.12.0;

    Wednesday, August 14, 2013 6:36 PM
  • Hi Arthur,

    Thanks for your response.

    Yes, it looks like SSIS still has bunch of issues with Excel files like as follows:

    1) Some times excel source files cannot be parsed by excel source connection manager.

    2) For derive column conversion, we can't replace the existing column. rather, we have to add the derived column as " add as new column" which is tough to manage  while destination mapping.

    3) If an column in excel contains a data which is not of the data type assigned for the column in excel, the excel source reads that data as "null". For the same, we can't validate the data and redirect the erroneous data in reject file.  <-- For this we tried IMEX setting also

    4) In multi-tab/sheet excel file, excel source is unable to detect a tab and identify the metadata of the excel.

    I am also checking Microsoft connect for Excel issues with SSIS 2012(https://connect.microsoft.com/SQLServer/SearchResults.aspx?SearchQuery=excel#&&PageIndex=22 )

    As worst case scenario, I am thinking of converting Excel to CSV file or Flat text file. (http://www.mssqltips.com/sqlservertip/2772/importing-data-from-excel-using-ssis--part-2/). Do you think it is advisable to convert Excel into CSV or Flat file.

    Thanks,

    Ketan

    Thursday, August 15, 2013 7:16 AM
  • Unfortunately Excel was not meant to be the data transfer media. It is a desktop UI centric application which ended up on every machine and now SSIS needs to embrace this challenge. Of course user input in an incorrect format may break any package.

    Re #2, I see why not, doable with the proper SSIS expression.

    3: You can deal with nulls using SSIS expressions. But you need to assign proper datatypes, the issue mostly has to do with the shallow data sampling by the Excel Driver.

    4: The Excel connector does see all the tabs (unless they have special chars as #,$ etc.)

    CSV is more manageable, ETL solutions involving CSV files are cheaper.

    Most issues though are very well covered by now and it seems that you can find an answer to every nuance.

    PS: Todd McDermid has several good posts that cover many gotchas using Excel in SSIS http://toddmcdermid.blogspot.ca/


    Arthur My Blog

    • Marked as answer by Ketan Patil Friday, August 16, 2013 11:46 AM
    Thursday, August 15, 2013 3:00 PM
  • Hi Arthur,

    Thanks for alerting me on Todd's blogs. I usually check SSIS blogs but again any info is valuable to me.

    Regards,

    Ketan

    Friday, August 16, 2013 11:52 AM
  • I found a solution to a similar problem here: http://imgalib.wordpress.com/2013/01/24/how-to-solve-ssis-error-code-0xc020801c0xc004700c0xc0047017/

    In short, you need to select Project from the menu, choose properties, then from the property pages choose Configuration Properties, then debugging page. Select false for the Run64BitRunTime property.

    Hope this works for you

    Iain

    Sunday, January 12, 2014 2:16 PM
  • This solution also allowed me to connect to a 32-bit Access DB.

    Thanks, Scott

    Friday, January 31, 2014 3:32 PM