Misadventures of SSIS Excel Provider Part 1 - 64 bit land
This is the first post in the series and it addresses retrieving data from Excel file in 64 bit.
Most of you who have used SSIS and had to load data from Excel files have probably encountered the dreaded 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine error message. The most common answer to this problem is to switch your package execution to 32 bit mode. However I guess there are a lot of developers who would like to be able to load data from Excel in 64 bit mode without usage of 3'd party tools. If you're one of those developers, I hope my post will prove helpful.
This solution uses only native SSIS components and no custom programming via script component is needed.
Things to know about 'Microsoft.Jet.OLEDB.4.0' driver
- This driver is no longer supported by Microsoft
- It is not usable in 64 bit runtime
- It will read formatted values instead of actual values when loading data (e.g. '1.23457E+19' instead of 12345678945654600000)
The key to loading Excel in 64 bit mode is using 'Microsoft.ACE.OLEDB.12.0' provider with OLE DB Connection Manager.
'Microsoft.ACE.OLEDB.12.0' is newer, supported by Microsoft, available in 32 and 64 bit and preferred driver for reading data from Excel files.
As this driver is an OLE DB driver, a simple OLE DB connection manager can be used to read data.
However as SSIS designer doesn't support creating connection manager for Excel using 'Microsoft.ACE.OLEDB.12.0' provider, a small workaround has to be done
- first specify OLE DB connection manager for a simple file
- and then manually change connection string to use 'Microsoft.ACE.OLEDB.12.0' provider. Connection string to use can be found at www.connectionstrings.com
One other thing to be aware of is that 'Excel Source' can't be used in Data Flow Task when using 'Microsoft.ACE.OLEDB.12.0'. Even though the driver is supported in 64 bit mode, 'Excel Source' component is not. So just use OLE DB source without loosing any functionality.
For those who need a walk through, here's a step by step instruction on how to load data from Excel using 'Microsoft.ACE.OLEDB.12.0'.
Prerequisites:
- 64 bit development machine
- SQL Server Business Intelligence Development Studio
- 'Microsoft.ACE.OLEDB.12.0' 64 bit driver installed
Please be aware that IF you have 32 bit Office installed on your development machine you won't be able to install 'Microsoft.ACE.OLEDB.12.0' 64 bit driver. However you can test your package in 32 bit runtime while developing and have 64 bit driver installed on test and production servers.
Office is not needed for this driver to work, only restriction is that IF you have Office 32 bit installed on 64 bit machine, you won't be able to install 64 bit driver.
Office is not needed for this driver to work, only restriction is that IF you have Office 32 bit installed on 64 bit machine, you won't be able to install 64 bit driver.
1. First of all create at least two files for the purpose of this sample.
2. Open SQL Server Business Intelligence Development Studio.
3. On Start Page click 'Project...' at the 'Create:' label. 'New Project' window will open. In the window:
3. Package.dtsx designer window will open up.
Right click 'Connection Managers' area and select 'New OLE DB Connection...'
4. In the 'Configure OLE DB Connection Manager' window click 'New Button'.
5. In the 'Connection Manager' window expand Provider drop down list and select 'Native OLE DB\Microsoft OLE DB Simple Provider'. Enter path and file name of the 'DummyTextFile.txt' in the 'Server or file name:' box and click 'OK' button to close 'Connection Manager' window and 'OK' one more time to close 'Configure OLE DB Connection Manager' window and create connection manager.
6. You'll see that a connection manager 'C:\Temp\DummyTextFile.txt' was added to 'Connection Managers'. Rename it to 'ExcelCM'.
7. Right click the 'ExcelCM' connection manager and click properties. Replace existing ConnectionString with this text Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Excel_97-2003_SourceFile.xls;Extended Properties="Excel 12.0;HDR=YES;IMEX=1";
8. Add a 'Data Flow Task' to designer area.
9. Double click the 'Data Flow Task' and add 'OLE DB Source' to the designer area.
10. Double click the 'OLE DB Source', select 'ExcelCM' as OLE DB Connection manager, expand 'Name of the table or the view:' drop down list and select 'Sheet1$' from the list. Click 'Preview...' button to make sure the data can be read successfully.
11. Add any preferred destination and run the package.
I didn't have much time writing this post, so if you have any questions regarding reading data from Excel in 64 bit mode, please post them in comments and I'll update this post to include additional information.
Next post in the series Misadventures of SSIS Excel Provider Part 2 - Handling data types
- DummyTextFile.txt is just an empty text file and the only reason to have it is to set up OLE DB Connection.
- Excel_97-2003_SourceFile.xls is the actual file that will be loaded.
2. Open SQL Server Business Intelligence Development Studio.
3. On Start Page click 'Project...' at the 'Create:' label. 'New Project' window will open. In the window:
- Select 'Business Intelligence Projects' on the left side and 'Integration Services Project' on the right.
- Enter 'Excelx64Project' as project name and 'Excelx64Solution' as solution name
- Click 'OK'.
3. Package.dtsx designer window will open up.
Right click 'Connection Managers' area and select 'New OLE DB Connection...'
5. In the 'Connection Manager' window expand Provider drop down list and select 'Native OLE DB\Microsoft OLE DB Simple Provider'. Enter path and file name of the 'DummyTextFile.txt' in the 'Server or file name:' box and click 'OK' button to close 'Connection Manager' window and 'OK' one more time to close 'Configure OLE DB Connection Manager' window and create connection manager.
7. Right click the 'ExcelCM' connection manager and click properties. Replace existing ConnectionString with this text Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Excel_97-2003_SourceFile.xls;Extended Properties="Excel 12.0;HDR=YES;IMEX=1";
8. Add a 'Data Flow Task' to designer area.
9. Double click the 'Data Flow Task' and add 'OLE DB Source' to the designer area.
10. Double click the 'OLE DB Source', select 'ExcelCM' as OLE DB Connection manager, expand 'Name of the table or the view:' drop down list and select 'Sheet1$' from the list. Click 'Preview...' button to make sure the data can be read successfully.
11. Add any preferred destination and run the package.
I didn't have much time writing this post, so if you have any questions regarding reading data from Excel in 64 bit mode, please post them in comments and I'll update this post to include additional information.
Next post in the series Misadventures of SSIS Excel Provider Part 2 - Handling data types
(link will be added when post is available)
Aidas, nice post. My note is not related to your post directly, but your situation is too simple because of the server with Office installed. From my experience there is no such server on any production environment. And no administrator is willing to install Office on already running production server. But that is problem for your next or next to next post :).
ReplyDeleteThis comment has been removed by the author.
DeleteHi, Thank you for the comment.
DeleteI probably have not explained the situation eloquently enough - there's no need for Office on the server and there shouldn't be, but if someone had office 32 bit on their 64 bit server, then they wouldn't be able to use the 64 bit driver.
Post corrected to more clearly explain the situation.
Nice post mate, keep up the great work, just shared this with my friendz
ReplyDeleteexcel courses