Aim :- What is the problem with Excel Source in SSIS 2012 and how can we overcome this issue?
Description :- I don’t know how many of you know this and ever found this problem with Excel Source in SSIS while working on MS SQL Server Integration Services – SSIS 2012. So far, I struggled with this issue for two days and finally I found the solution through intense web research from Google. So, I thought to share my experience with you all in the form of article. I will precede step wise, explaining problem first and then will provide solution to this problem with Excel Source.
I tried to move data from the Excel source in SSIS to a table and at that time I faced the following error.
Error Message :-
[Excel Source ] 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.
[Connection manager “Excel Connection Manager”] 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.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.
NOTE :- Now we are all going to see how this error comes into picture with a small example.
Suppose, I have some data in excel I want to move that data to SQL Server. So my source is excel and destination is SQL table, for doing this demo we can take one Excel source for source data and one OLE DB destination to capture source data.
Now, we can go through practical way, Open BIDS and take new package, name it as EXCEL TO TABLE.
STEP 1. In control flow, take one Data Flow task and within that Data Flow task take Excel source and OLE DB destination.
After this, To configure the Excel source in SSIS, double click on the Excel source. Double clicking the Excel source in SSIS will popup the following Excel source editor wizard.
STEP 2. Click on the New button and then click on browse.
- Select a Source path and click OK.
- Choose name of the Excel sheet as ‘Sheet1’ and then Click on columns and click OK.
- Next take OLE DB destination and establish connection between Excel source and OLE DB destination.
- After this, Configure OLE DB destination, for doing this just.Double click on OLE DB destination.
- Go to create connection manager and click New.
- Give database name as :- PHPRING and the Data access mode will be :- table or view.
- Name of the table or view :- click on new to create table and change table name as [EXCEL TO TABLE].
Follow the below screen shot to see all the above mentioned things which I performed at the time of configuration.
STEP 4. Now, our package is ready to execute, while executing this package it fails due to some error, let us see what that error is? And later we can do some modifications to overcome this problem. Save and press ‘Execute’ button or directly press F5 and see what’s going to happen at the time of package execution.
Error Message :-
[Excel Source ] 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. And it also failed due to 64 bit Driver.
Let’s analyze what the error says :- You need to install the 32 bits provider.
Even though your window is 64 bits, the SSDT development environment is a 32-bit environment. The reason why you can’t find the provider in the list of providers is because it only shows 32-bit data providers. You can only see 64-bit providers that have a 32-bit version available on the development machine. This is the one solution to overcome the above issue and we can also overcome the same issue with following procedure.
The error looks like big, but the solution is very simple. To overcome the above issue, you can follow the below steps :-
Once we click on properties the following window will appear.
In the above window, go to the configuration properties and click on the Debugging option. Once we click on debugging option, the below window appears. In this window, we have one option like “RUN64BITruntime =true”. To overcome this problem just set it to FALSE, Isn’t it Simple?
STEP 6. Click on the apply button and then click OK. Now, save the package and press f5 to execute it.
By looking at the above screen shot, we can conclude that the package is executed successfully without any issues.
Finally, we can open SSMS to see whether our Source data is moved to the Destination or not, for doing this just open SSMS with following query.
Select * from [EXCEL TO TABLE]
In the above screen shot we can clearly see our source data in excel is moved to destination successfully.
Conclusion :- We can conclude this whole story in simple and short steps as –
- Right click on your SSIS project in your “Solution Explorer”.
- Then Choose “Properties”.
- On the “Property Pages” click on “Debugging”.
- Under “Debug Options” ->”Run64BitRuntime”.
- Switch it from “True” to “False”.
- Now, project will use 32 bit run-time at the start , even if 64 bit SSIS run-time is installed.
I hope you all enjoyed this article on problem with Excel Source in SSIS 2012, if you have any further queries just drop your doubt as a comment. We will be happy to help you. Please provide your feedback – It helps us to strive for Perfection.