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

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.

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;

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