asked on

How can I Determine in PowerShell if I have Components for OLEDB.12 Connectivity for SSIS

I moved an SSIS package to a different box and got the error "The 'microsoft.ace.oledb.12.0' provider is not registered on the local machine." I did some research and found a link to the Microsoft site to download the components and everything ran fine.
I am executing out of a PowerShell script, so I thought it would be nice to check up front for the installation of the components. I added the code:
if (((gci HKLM:SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall | gp).displayname) -contains "Microsoft Office Access database engine 2007 (English)" ) ...
and it worked on that machine. So, I thought I'd test it one mine, from which I've regularly been running the package. It reported I do not have the above installed, but everything runs, and I don't get any errors. Which means I have something installed that provides the drivers, thus I need to check for more than one item, but I am not sure what else provides the necessary components to import data from Excel and Access. I am not sure I can identify all, but I'd hate to tell someone they need to install something if they don't.

Does anyone know of another (reliable) way I can determine if I would have to proper components installed to allow me to import data from Excel and/or Access? I'd like to halt the script before I kick off the package and it reports an error during execution because it cannot connect to the data source.

Qlemo

What provider are you exactly providing as source in SSIS?
You'll also have to take care of x64 and x86 differences.
The "2007" check in registry is too restrictive, the 2010/2013 drivers will work, too, and probably that is what happens on your machine.

Qlemo

gci HKLM:Software\Wow6432Node\ODBC\ODBCInst.Ini,
    HKLM:Software\ODBC\ODBCInst.Ini -EA SilentlyContinue |
   ? { $_.PsChildName -like "Microsoft Access*.accdb" }

Open in new window

should get you a result whenever the 2007 or above drivers are there. It's ok if you do not need to care about x64 and x86.. Else you will have to get some more logic into it.

D B

ASKER

I've requested that this question be deleted for the following reason:

Given the response, and that there can be different providers, we are going to rely on 'hope' that the appropriate drivers are installed. The code is recoverable if it crashes and they need to install them after the fact if an error occurs. Thx.

Qlemo

Sorry? I've provided you with advice and even exact code as requested - that you do not want to go down that road doesn't change it from being correct and appliable.

D B

ASKER

qlemo's solution did not return any results, running it both on my local machine, or on the server that first reported the error. Given other responses, and the fact that his solution did not return anything, I made the decision to request deletion of the question, rather than pursue it. Acceptance, as excellent, of a response that does not return anything was premature, and misleading. I request that either the question be deleted, or his response not be flagged as the accepted response and the question be pursued to a workable solution.

D B

ASKER

Also, since it is SSIS, I assume we will need the 32-bit drivers, as we are running the 32-bit version of DTSExec.exe.

ASKER CERTIFIED SOLUTION

Qlemo

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

D B

ASKER

Thank you. This works. I've tested on three machines. One is my local, which is a full development machine, one server in which I installed the required drivers per the error message, and on a server that does not have the drivers installed. Received the expected results. Thank you.

Qlemo

Great! I like that code because it is simple and reliable, and hence does the job very well.