Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Posts
    23

    Unanswered: automate copying access table to sql server 2005 db

    I have a table in MS Access that I would like to copy/import into SQL Server 2005. I would like to automate this process so that it copies the table over on a daily basis. Can someone tell me how this can be done?

    Thank you in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look at DTS packages which you can schedule as a job - nice and easy
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by georgev
    Take a look at DTS packages which you can schedule as a job - nice and easy
    I'm sure you meant SSIS
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SSIS? I've not come across that acronym before...
    I figured for someone new at SQL Server, a scheduled DTS would be the easiest solution...
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    DTS is with 2000,
    SSIS is with 2005

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In 2005 (Server Management Studio)
    Select database
    > Management
    > Legact
    > Data Transformation Services
    *confused*
    George
    Home | Blog

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    key word is "Legacy" here. the new ETL component in 2005 is SSIS, it's been completely re-architected.

  8. #8
    Join Date
    Jan 2007
    Posts
    23

    ssis and accomplishing my task

    How do I use SSIS exactly to accomplish my task (see orig question posted).

    Thanks.

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by needInfo25
    How do I use SSIS exactly to accomplish my task (see orig question posted).

    Thanks.
    Use SSMS. Right-click the database to which you want the data imported. Select Tasks | Import Data. Follow through the wizard and be sure to save the SSIS package to your DB server.

    Then you will need to create a scheduled job to run the SSIS package on whatever schedule you want.

    Notes:
    If the Access db is located on a server other than the DB server, you'll need to use UNC paths (and probably an ODBC link, but it's been a while since I've had to use Access)

    SSIS is a separate component of the SQL 2005 installation. If you have not installed it, it won't be available to you.

    Regards,

    hmscott
    Have you hugged your backup today?

  10. #10
    Join Date
    Jan 2007
    Posts
    23

    thanks

    That was the answer I was looking for. I created a SSIS Package and created a job for it. Seems to work nicely. Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •