Wednesday, December 9, 2009

Load MS Access data to SQL server

Question: How to transfer MS Access data to Sql Server 2005?

There are 2 ways for achieving this
1) SSMS: Import & Export Wizard
2) BIDS: Create a New SSIS package

First approach is easy and gives you flexibility to modify the SSIS package(If required)
I hope you can try this your self .

Let me know if you need my help for this.

Second approach: 
Open the BIDS and create a new package

a) Create a new OLE DB Source connection.
b) Select the provider as "Native OLE DB\ Microsoft Jet 4.0 OLE DB Provider".
c) Select the Database file name. [Check the Image Below]

a) Take a DFT.
b) In DFT drag "OLE DB Source", "OLE DB Destination" [or take any destination as per your requirement]
[Check the Image below]

Before executing the package please makes sure that destination table should have the correct data type. [if not use data conversion task]

I hope this will help you. If you need more assistance or clarification, please leave the comment.