How to load more than one MS-Access-db tables with data in SQL-Server-DB

Question

I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service.

One of my computer folders contains more than one MS-Access db, want to load all of this access tables with data in SQL-Server db. To load all tables I want to use ssis. Which control of ssis will help me to load all access tables on my SQL-Server db?

I want to create (SSIS) package for importing an MS Access table into SQL Server, then running some stored procedures on this database and finally exporting the result to Excel.

I am comfortable in writing queries and working in Management Studio but I don’t know how to actually design an SSIS package. Need helping hand to do this?

If have any query please ask.

Answer 1

In SSMS, you can right click on your target database, select Tasks...Import Data, then choose your Access database as the Source and then choose your target SQL Server database as the Destination.