Sql SErver 2012 - Import .dbf files and Access 2007 files

Windows Server 2012 R2
SQL server 2012

We are moving our data systems from SQL 2008 to 2012.  In Sql 2008 (on Windows 2008) I import:

- .dbf IV files using the Visual FoxPro driver
- Access 2007 files using the Microsoft Office 12.0 Access Database OLE DB Provider

Testing on the new Sql Server 2012 I do not see either of these 2 drivers in the Data Source list (Import wizard).  

On our Windows 2012 R2 server, I downloaded the Visual FoxPro driver and installed it.  I can now see it on the list, but as soon as I choose "Properties", the wizard crashes and asks to be closed

For Access, I downloaded and installed the AccessDatabaseEngine driver for both 2007 and 2010 (32 bit).  The install seemed to go fine and I see both in "Add Remove programs", but I DO NOT see it in the SQL Server Data Source list (Import wizard).

HELP!  As I understand, if I can get the "Microsoft Office 12.0 Access Database OLE DB Provider" to show properly, I might be able to import both my Access 2007 tables AND my dBase 4 files.  Why can't I see it on my Import wizard Data Source list?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to make sure to use 32bit Import Wizard for importing. If using the SQL Server itself for import, driver bitness has to be the same as SQL Server one.
Crashing Import Wizard is obvious behavior in conjunction with older drivers...

The easiest way for DBFs is to export data from simple FoxPro application to SQL Server. You will create a loop, read DBF row by row, and generate INSERT commands on SQL Server. This is possible on 10 lines of code. Everything you need is Visual FoxPro or its free run-time and SQL Server native client. And basic knowledge of FoxPro programming, of course.

MS Access Data engine (64 bit OLE DB provider) should allow to create Linked Server on SQL Server and then you may query Access data from SQL Server directly. It was also possible for DBFs but 64 bit world is not suitable for FoxPro...

Important prerequisite for drivers visibility is equal bit depth, so 64 bit SQL Server/Import Wizard needs 64 bit drivers. Unfortunately, you cannot install both 32 bit and 64 bit Access drivers on one machine. Or this was valid for Access 2010 at least.
Access 2016 does not support DBFs...
MaxwellTurnerAuthor Commented:
On our 64 bit Windows 2008 server I have always used the 32bit Visual FoxPro driver, and the SQL Server import wizard worked fine.  On our 64 bit Windows 2012 server, the 32bit Visual FoxPro crashes ... I simply tried to re-create what I was doing on 2008 on 2012.  It seems that the FoxPro driver has been deprecated for SQL Server 2012.  

I ended up creating a simple SSIS package in Visual Studio with a basic OLE DB Source and Destination for each table.  Strangely, the Visual FoxPro driver worked fine in SSIS.  

Thanks for your replies, but I now have my tables importing via SSIS package.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
"the 32bit Visual FoxPro crashes" this is not true because the Import Wizard crashes... and Visual FoxPro works well in this environment.

Anyway, it is good to read SSIS package works for you.
MaxwellTurnerAuthor Commented:
The advice given was good, but I found an alternate way of getting the files imported before I was able to review the answers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.