SSIS 2017 connection to Access .accdb file

Question

SSIS 2017 connection to Access .accdb file

pietlinden

SSC Guru

Points: 63503
More actions
February 24, 2019 at 11:31 am

#382816

Sorry, I'm an SSIS noob. I've connected to .MDB files in the past using the Jet 4.0 connector, and it worked fine. The newer .Accdb format doesn't use the Jet, though, so what connector am I supposed to use? (I looked and tried several, but no joy!)
I was going to write a For Each File loop and pull the data out of my databases and write it to SQL Server, but thus far no joy. I could do this in Access with a linked table, but for some reason I can't get find a provider for OLEDB that actually works.
What am I missing?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

you need Microsoft Access Runtime (ACE)
https://www.microsoft.com/en-ie/download/details.aspx?id=13255
https://www.microsoft.com/en-us/download/details.aspx?id=26602 -- sp1

This will also help you https://www.connectionstrings.com/

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Thanks Federico. I downloaded that and installed it,
I created a connection using the Source Assistant and could only connect to the database at all using the SSIS provider. But then when I tried to connect to a table or view, I get the message "No tables or views could be loaded". (Is MSFT just yanking my chain? I could push the data from Access to SQL Server in a matter of seconds.) So I can connect read from SQL Server to Access just fine. And I can write to SQL Server through the linked table, but this is baffling.
Error:

"Could not retrieve the table information for the connection manager.
<path to source db>
The requested collection is not defined"

I'm confused. I can read Access data from PowerBI just fine, and from Excel etc.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Do I seriously have to create a DSN for this to work?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

no need for DSN.
Using SSIS, add a OLEDB or a ADO.NET connection. point it to your access file, and set other properties on it as required.

As with all other connections/sources in SSIS working with variables for connection strings work in exactly the same way, including table name to query.

Just one minor thing - in order to use this while developing you need to install the 32 bit version as BIDS/SSDT/Visual Studio is 32 bit.
When moving to the server use the 64 bit version.

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Oh, okay. I'll give it a try. (Remember when MSFT used to be really explicit about which version of a program was installed and you could go to help>about and it would tell you if you had a 32-bit or 64-bit version installed? Where did they hide that on SSDT?)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

bids/ssdt/visual studio is always 32 bit.

when I said you had to install the 32 bit version I meant the 32 bit version of the Access Runtime

pietlinden SSC Guru Points: 63503 More actions · Answer 7

frederico_fonseca - Sunday, February 24, 2019 3:01 PM
bids/ssdt/visual studio is always 32 bit.
when I said you had to install the 32 bit version I meant the 32 bit version of the Access Runtime

Oh... MSFT you GOTTA be kidding me... Can't install a 32-bit version of Access Runtime with 64-bit apps installed.
I give up. I'll install all 32-bit apps on my 64-bit OS because who ever needs access to more than 4GB of memory?

I rest my case, Your Honor.

Thanks for the pointers!

frederico_fonseca SSCoach Points: 16154 More actions · Answer 8

pietlinden - Sunday, February 24, 2019 3:08 PM
frederico_fonseca - Sunday, February 24, 2019 3:01 PM
bids/ssdt/visual studio is always 32 bit.
when I said you had to install the 32 bit version I meant the 32 bit version of the Access Runtime
Oh... MSFT you GOTTA be kidding me... Can't install a 32-bit version of Access Runtime with 64-bit apps installed.
I give up. I'll install all 32-bit apps on my 64-bit OS because who ever needs access to more than 4GB of memory?
I rest my case, Your Honor.
Thanks for the pointers!

not quite.

Access runtime 32 bit can not be installed if you already have a Office install of 64 bit (or the other way around).
Apart from that yes you can have both 32 and 64 bit apps installed - just that they won't mix directly.
From office applications the only one that really makes use of 64bit is Excel - do you really have HUGE spreadsheets that would require it to be 64bit?
And we talking about workstations - for servers everything should be 64 bit - and Office on servers is not supported (note that the access runtime is not really part of office for this purpose).

pietlinden SSC Guru Points: 63503 More actions · Answer 9

Okay.
SSIS just baffles me. How much RAM am I supposed to have for this? If I open a Script task, it takes like 15+ seconds to close again. WTH???!!! Maybe SSIS just hates me.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 10

It is confusing to create a link between Access and SQL Server.
I have an old application created in Access 2010 .mdb format.
I have an Windows 2010 computer with 64 bit OS and Office 365 32 bit installed.
I tryed to install the 32 bit version of:

Strange.
I did install the 64 bit version and successfully linked the .mdb file to SQL Server.
But I was not able to link a database created in the install Office 365 Access program
.accdb format. You cannot open the older .mdb file in Office 365. I am not an Access man
but is there an updated version of "Microsoft Access Database Engine 2010" etc 2016?

Lowell SSC Guru Points: 323518 More actions · Answer 11

you can install the 32 bit side by side if you execute the package with an optional flag.

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine.exe /passive
command line flag;

this will force the install of the drivers, even if you have 64 bit office installed;
otherwise you get some error about Office preventing the install.[/quote]

this works the opposite way too. if you have 32 bit ACE drivers/office installed, and want to install the 64 bit:
http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

o.schoen SSC Veteran Points: 211 More actions · Answer 12

Adding to the questions rather than the solutions:
Reading the caveats on the Access Database Engine install (link in Lowell's post above): it is NOT supposed to replace the JET OLEDB provider server-side, should only be run in a logged on user account ant not by a server job. This sort of cancels its usefulness for agent scheduled SSIS application, or am I being overly sensitive? If this one does not: what solution does serve?

Thanks for any further leads or pointers to instruction how to implement this correctly.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 13

o.schoen - Thursday, February 28, 2019 3:15 AM
Adding to the questions rather than the solutions:
Reading the caveats on the Access Database Engine install (link in Lowell's post above): it is NOT supposed to replace the JET OLEDB provider server-side, should only be run in a logged on user account ant not by a server job. This sort of cancels its usefulness for agent scheduled SSIS application, or am I being overly sensitive? If this one does not: what solution does serve?
Thanks for any further leads or pointers to instruction how to implement this correctly.

although it does say that it is indeed what should and is used on servers

o.schoen SSC Veteran Points: 211 More actions · Answer 14

frederico_fonseca - Thursday, February 28, 2019 3:40 AM
o.schoen - Thursday, February 28, 2019 3:15 AM
Adding to the questions rather than the solutions:
Reading the caveats on the Access Database Engine install (link in Lowell's post above): it is NOT supposed to replace the JET OLEDB provider server-side, should only be run in a logged on user account ant not by a server job. This sort of cancels its usefulness for agent scheduled SSIS application, or am I being overly sensitive? If this one does not: what solution does serve?
Thanks for any further leads or pointers to instruction how to implement this correctly.
although it does say that it is indeed what should and is used on servers

Thanks for the advice!
Will try that on the next update cycle...