Oct
15
2013

Problem with Excel Source in SSIS 2012

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.

problem with excel source in SSIS and Solution to the issue

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 [2]] 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: 0×00000000.

An OLE DB record is available.  Source: “Microsoft OLE DB Service Components” Hresult: 0×80040154 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.

Excel Source in SSIS to OLEDB Destination in SSIS

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.Excel Source Editor
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.

OLEDB Configuration
STEP 3.Next click on the Mappings button, it will internally map Source columns to Destination columns. Follow the below screen shot for clear observation.

SSIS Mappings
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.

Execution Error Excel souce in SSIS to OLEDB destination
In the above screen shot we can clearly observe that excel source is failed due to some error, Open the progress tab to see the error.

Error Message :-

[Excel Source [2]] 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 :-

STEP 5. Go to the solution explorer and Right click on Project name. Then click on the properties option.Integration Services Project Properties

Once we click on properties the following window will appear.

Integration Services Project Property page

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?

Debugging Property SSIS

STEP 6. Click on the apply button and then click OK. Now, save the package and press f5 to execute it.

Successful execution of the package

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.
use PHPRING
go
Select * from [EXCEL TO TABLE]

Output in the 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 10.0/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +8 (from 8 votes)
Problem with Excel Source in SSIS 2012, 10.0 out of 10 based on 6 ratings
Enjoyed this post or Have Queries ? Please consider leaving a Comment below

Related Posts

About the Author: Avinash Munnangi

Am always a Learner. My aim is to learn something new of every single day, Learning one thing in different views is always Fun!!!

2 Comments+ Add Comment

  • Hi,
    Impressive post !!!
    Thanks for sharing the solution Avinash.
    Phpring is the great source of learning for Beginners … :)
    Great work !!!

    • Hai Kelsang,

      Hearty welcome to PHRING .very glad to hear your kind of words ,thanks for your support
      we always do our level best to give such type of articles to folks glad you liked it…

Leave a comment

Current day month ye@r *