Can’t run SSIS Package in 32-bit (grrr)
On a modern 64-bit Windows Server, it’s not difficult to stumble across this old chestnut when trying to run a new (perhaps an Import Data Wizard) or existing SSIS Package:
The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine.
Thanks to Microsoft confuddle, it’s easy to get sidelined into messy fixes which damage office or worse.
Ultimately, to save head-scratching, this is actually down to Office 32-bit not being installed on the server – and why should it? But is seems odd when Office 64-bit or even just Excel is installed, and it doesn’t install a full suite of data drivers. In fact, why doesn’t SQL Server itself (considering it comes with SSIS) simply install the full suite of data drivers, be they 32-bit or 64-bit? Worse, SSMS itself only comes as a 32-bit app, so that often causes such issues, because that is what is causing the clash. Never mind, let’s move on…
You can check the installed
EXECUTE MASTER.dbo.xp_enum_oledb_providers
<SHOW QUERY RES>
There are plenty of articles about this problem, for example the trusted Pinal Dave. Microsoft themselves do advise just installing the Microsoft Access Database Engine 2010 Redistributable. But then you’ll end up with an installation which may well (and it does) interfere with Office as well as then requiring tons of constant Windows Updates to maintain Office 2010 – somewhat overkill! So instead, thinking about the reasons, you could just create the SSIS packages using the wizard which you’ll find under SQL Server in your Start Apps:
SQL Server 2014 Import and Export Data (64 bit)
Or whatever version you’re on of course. Sorted – the Wizard now works.
If you’re trying to run an existing SSIS package, you can still use it, but have to be careful with the DTExec because by default it will again try to run as the 32-bit version. So use PowerShell in a .ps1 file to first change to the 64-bit directory before calling the package:
Set-Location "C:\Program Files\Microsoft SQL Server\120\DTS\Binn" DTExec.exe /SQL "SSIS Wiz Import Base Data 001"
The ISE edition of PowerShell makes it easy to select individual lines if you have a suite of package calls – cool. Also, it’s actually easier to copy and paste the whole log of package call results in ISE, whereas the SSMS GUI spits out nice lines, but they have to be individually copied and pasted if you need to dig deeper. Plus you can write your own logger etc in PowerShell.
Anyway, that generally solves the issue, let me know if you still have problems – happy data wrangling!