How to import ".mdb" files into SQL Server 2008?

Question

Assuming it can be done (as I've been told):

How do I import ".mdb" (MS Access / OleDb) files (specifically, Northwind sample database files) into SQL Server 2008 (Express with Advanced Features)?

Answer 1

Using SSIS, Bob. You'll create a package in BIDS using the OLEDB connection manager and map the data into wherever you want it to go. See also: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/710cacdd-636f-473d-af69-d28b656a9851/ and other threads in the SSIS forum.

There's also the SQL Server Import and Export Wizard (but I haven't tried this for years):

1. Open SSMS.
2. Create a new empty database.
3. Right-click the database in the Object Explorer.
4. Select Tasks | Import Data... from the context menu.
5. Change the data source to Microsoft Access.
6. Browse for the file name.
7. Click Next...
8. That's where my memory starts to get fuzzy. ;-)

It would be easier, though, to use the T-SQL scripts @ Code Gallery to create Northwind on your instance: http://code.msdn.microsoft.com/northwind

---

David Reed - MSFT - Microsoft Certified Architect|SQL Server - http://blogs.msdn.com/reedme/

Answer 2

Hi there, as for importing .mdb files into SQL 2008, I've noticed a MS file converstion utility http://www.microsoft.com/downloads/details.aspx?FamilyID=133b59c2-c89c-4641-bebb-6d04476ec1ba&DisplayLang=en or for SQL 2005 http://www.microsoft.com/downloads/details.aspx?FamilyID=4ecd1e67-c64e-49e6-821e-c4d83d9d5fed&DisplayLang=en

Rich

---

Rich