How to schedule "Import Data" from MSAccess to sql server

• praveenkumarpalla

  SSC-Addicted

  Points: 475

  More actions

  February 21, 2010 at 11:09 pm

  #230200

  I need a script or any which should import data from ms access to sql server (in existing database and tables) mainly specific tablels from ms access, table names will be the same and why script means every day in the morning i need to import data from ms access tables to sql server tables since using these sql server tables there is scheduled operation (report generation) in sql server tables.

  Can any one help me in doing this

  Thanks in advance,

  Praveen Kumar Palla.

• Jack Corbett

  SSC Guru

  Points: 184388

  More actions

  February 22, 2010 at 6:50 am

  #1122913

  You could do this a couple of ways both of which will require that you setup a SQL Server Agent Job to schedule the execution the process:

  1. Use SSIS as this is what it is designed for. An MS Access source and a SQL Server destination

  2. Use OPENROWSET/OPENQUERY

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• praveenkumarpalla

  SSC-Addicted

  Points: 475

  More actions

  February 24, 2010 at 1:27 am

  #1124147

  Thanks for the response

  I am having issues when using integration services project from sql server...

  USing SSIS how can i import data from MS Access to SQL Server

  Can you please suggest me or any sample?

  Thanks in advance

  Praveen Kumar Palla

• Jim Russell-390299

  SSCrazy Eights

  Points: 9333

  More actions

  February 25, 2010 at 4:52 am

  #1124855

  Is something preventing you from simply linking to the Access DB as an external linked server? Then you simply work with the Access data as you would after (an unnecessary?) batch transfer to some SQL Server staging table?

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  February 25, 2010 at 5:27 am

  #1124877

  praveenkumarpalla (2/24/2010)

  ---

  Thanks for the response

  I am having issues when using integration services project from sql server...

  USing SSIS how can i import data from MS Access to SQL Server

  Can you please suggest me or any sample?

  Thanks in advance

  Praveen Kumar Palla

  The easiest way to use SSIS for a simple import is to use the Wizard..

  on SSMS rirght click on the datasee --> Tasks --> import data

  You can then save this package and edit in BIDS if neccesary.

  The wizard can also be invoked from BIDS