Excel SSIS and the 32bit Issue
We have a lot of SSIS packages that import data from Excel. As well as all the fun that comes with importing Excel data in your packages. You also have a few tweaks you need to do on the SSIS server as well.
Install Excel Drivers:
Firstly you will need to install the Excel Driver 32bit. The easist way is to simply install Excel. simple enough.
Install 32bit DTEXEC
Secondly. As you need to run your packages in 32bit to use the Excel Driver. (you cant switch between 64bit SSIS driver then import with Excel 32bit. So the whole package has to run in 32bit) you will need to make sure you have the 32bit version of DTEXEC. If like me, you dont like installing client apps on your server. (BIDs, SSDT, SSMS etc) then for an SSIS server you will just install SSIS and possibily the Engine as well.
This means when its time to run your excel related packages, things dont go so well for you. The solution is of course to install the 32bit DTEXEC. which you can get via either installing BIDS/SSDT. or the drive via the Client connectivity tools.
The second option is, Microsoft have now released a set of 64bit drivers for the Office suit. This means you can just install those on the server and your Excel imports will work. without the need to install the 32bit DTEXEC. The problem then lies with you creating/testing/troubleshooting your packages. as you the reverse problem. BIDs and SSDT are 32bit applications. so they cant use the 64bit driver! so you will either have to have different versions in prod and dev or go with option 1.
The option i go with, is to use the 32bit option. and keep to the Andy Leonard style framework, where my imports are all done in 1 package and the transforms and the rest of the work are done in different packages. This means you are only in 32bit for a short time, and can keep your sanity with moving between prod and dev.