October 3, 2014 at 2:41 am
Hi there,
In SSIS I have a Project with multiple Packages running under it.
I have two particular pacakages. They both have an Excel 2007 Source.
One runs fine. The other returns an error of -
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.
I have gone to Project - Datacube (The project name) Properties - Config Properties - Debugging and set Run64bitRunTime to FALSE.
I would expect this to fix it for both - but it hasn't - as I said they are the same Excel Connection types (to different spreadsheets) but both the same file types - so bit at a loss why one works and the other doesn't.
Thanks
October 3, 2014 at 2:55 am
These are always difficult (frustrating and fun at the same time) to find and diagnose, and could be any number of issues, but these tend to be the main ones I've come across.
1) Are both Excel books in the same folder or path? If not check the SQL Agent account (or credential if you use a Proxy account) have access to that folder.
2) Is the failing job loading a sheet that is used by other people? If it is Check that it doesn't have a read lock on the file.
3) You might need to set the run time on the steps, this is done by clicking on the Advanced tab in the Step that is running the package, and checking the 32-but runtime at the bottom.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2014 at 3:01 am
Morning Jason.
Thanks for your reply.
1. The spreadsheets are both in the same location and both saved as XLXS.
2. The spreadhseet is not currently in use - but this one that fails is a Shared workbook. I unshared it though and it still failed with the same error. So can't be that.
3. Where is this advanced tab? I've highlighted the Excel Source in the Data Flow Task and can't see an Advanced Tab?
October 3, 2014 at 3:25 am
Sorry I think I misunderstood, are you running it through an SQL Agent job or in the development Environment?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2014 at 4:08 am
It's direct in SSIS that I'm testing it.
It's now working. I started the whole package from scratch??
Odd.
October 3, 2014 at 4:12 am
Very Odd, did you recreate the connection?
The only other thing could be that it was using the wrong driver, Or the connection file had become corrupt.
Knowing SSIS and BIDS, then anything is possible.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2014 at 5:29 am
I did recreate the connection.
Now I've published it to run over Integration Services from Management Studio, that is now failing. Trying to work that out now.
Never knew importing an EXCEL spreadsheet into SQL was so difficult.
October 3, 2014 at 5:32 am
Tried this and giving the server a reboot to see if that works -
"For files that have the Excel 2007 or later format, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine. This provider is installed automatically with the 2007 Microsoft Office system. If the 2007 Office system is not installed on the computer on which Integration Services is running, you have to install the provider separately. To install the OLE DB provider for the Microsoft Office 12.0 Access Database Engine, download and install the components on this Web page, 2007 Office System Driver: Data Connectivity Components. For more information on the file formats that Excel 2007 supports, see File formats that are supported in Excel."
So i've installed 2007 Office System Driver: Data Connectivity Components, onto the SQL Server to see if that gets me working. Will update if it does or doesn't
October 3, 2014 at 5:48 am
Still not working.
So just to confirm both jobs that import data from EXCEL 2010 into SQL rn from SSIS now. But now I have Deployed them to run via SQL Server Agent in Management Studio it now fails.
The error report I get reads -
Data Flow Task:Error: There were errors during task validation.
Data Flow Task:Error: One or more component failed validation.
Data Flow Task:Error: Excel Source failed validation and returned error code 0xC020801C.
Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
HR_EandD:Error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816
HR_EandD:Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
October 3, 2014 at 6:14 am
SORTED IT!!!!!
http://www.sqlhammer.com/blog/running-ssis-packages-in-32-bit/
Check out the above link and the SQL Agent “Use 32-bit” check box heading.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply