January 31, 2020 at 1:09 am
I'm trying to follow Daniel Calbimonte's article on importing data from Access to SQL Server. I can do it just fine if I use an .mdb file (2003), but if I try to use an .accdb file, I don't have the option to choose that from the OLEDB sources. How do I fix it? I tried installing the Access 2010 Redistributable thing, but it didn't help.
When I try to select Access (ACCDB) from the list of OLEDB sources, it's not there.
How do I figure out how to fix it? After forcing a silent install of the 32-bit drivers (I think), it works... well, the choice shows up. Now BIDS crashes after I specify the Access table I want to import from.
WTH??? Tried it several times, and every time I try to configure that, BIDS crashes. Pretty funny that even MSFT hates Access.
I can push data from Access2016 to SQL Server just fine, and I can import Excel files, but ACCDB import is a big fail.
January 31, 2020 at 6:34 am
thats SSIS for you - happens a lot unfortunately and I never managed to find a solution.
try with the 2016 runtime and see if it improves. https://www.microsoft.com/en-us/download/details.aspx?id=50040
if it still fails you may be forced to use a C# script as a component source on your dataflow where this will not normally occur - but is a bit more work for you.
January 31, 2020 at 10:22 pm
I think I tried the 2016 version. Any time I try to connect to an ACCDB, it fails. If I use Jet4.0 with an MDB file, it works fine. The other really weird thing is that Access apparently uses something like the NVARCHAR() data type for storing strings, so it wouldn't let me import text. After a while, I just got annoyed and quit.
The really annoying thing is that I can create an MDB with a linked table to the SQL Server DB and a link to the "source" Access DB and push the data just fine. But then I can't really schedule the stuff very easily like I can with SSIS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply