SSIS access provider

Question

I'm building an SSIS package on Microsoft Visual Studio Ultimate 2012 Trial Version to import an access database, but I can't see the correct provider (Microsoft Office 12.0 Access Database Engine OLE DB Provider) from the drop down when creating the connection string. I downloaded the AccessDatabaseEngine_x64 since the installed MS Office is a 64bit, I still can't see this provider that I'm looking for. I'm building this SSIS package on a Windows Server 2012 64 bit machine.

I need to know what I should do to be able to see this provider.

Can somebody help.

Answer 1

OK. this is mostly assumption but holds true for database drivers. I will quickly be downvoted if this is incorrect.

I assume your version of Microsoft Visual Studio Ultimate 2012 is a 32 bit app, so you cannot see 64 bit drivers.

Regardless of what type of install of Office you have, it will create an output file, and that file does not have 'bitness' - i.e. the same file is produced regardless of whether your office app is 32 bit or 64 bit.

You just need to match your SSIS runtime with your driver. So if your SSIS package will be running in 64 bit, you need a 64 bit driver to access an Office file.

If it will be running in 32 bit you need a 32 bit driver.

Normally you just install both versions, develop in 32 bit and run in 64 or 32 bit.