SQL Server Performance

Scheduling DTS to move data

Discussion in 'SQL Server DTS-Related Questions' started by simflex, Sep 23, 2003.

  1. simflex New Member

    Hello experts,
    this is my first time posting to this forum.
    I have 5 access databases that reside on a user's pc.
    These 5 databases need to be migrated to sql server database.
    The approach I was asked to follow was to first of all copy all data from these access databases into sql server databases and then schedule dts to append any new or modified data from these access databases into sql server database.
    Does anyone know of a script that can help me do this?
    Thanks in advance
  2. gaurav_bindlish New Member

  3. satya Moderator

    First off refer to this DBjournalhttp://www.databasejournal.com/features/mssql/article.php/1490561 article which helps to understand differences between the two.

    If this is one-of requirement simply you can use DTS to import the data from MS Access and I think using upsizing wizard in latest Access version is easier. Download appropriate driver fromhttp://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860 link to deal with import/export.




    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. satya Moderator

  5. gaurav_bindlish New Member

    I edited the same and now it is working.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  6. simflex New Member

    thanks gurus.
    I will be perusing the wealth information you provided.
  7. simflex New Member

    I have had time to dissect all the links you graciously provided to me.
    Though they provided information that was slightly different from what I was looking to accomplish, they were nonetheless very useful.
    What I was looking to accomplish was two-fold.
    One, find a way to export data from an access database that is currently sitting on the user's desktop.
    This data export needs to be automated so that on a daily basis, data from the access database will be exported to a text-delimited file.
    This is because the user will continue to use this access and we will need to use the exported data to design web apps.
    It would be nice to be able to use dts to extract data from the access db directly into sql server; better still, it would be nice to use upsizing wizard to accomplish same.
    However, it can't be done because the sql server db is on the remote server while user's access db is on his desktop.
    Now, I have figured out a way to use access macro to automate the export process and it seems to work so far.
    Right now, I need your help in coming up with either a stored proc or dts automation process to extract data from the text file where access data is exported to and into sql server db.
    Can someone please help here?
  8. satya Moderator

    a way to export data from an access database that is currently sitting on the user's desktop.
    .... can accomplish with DTS task and make sure SQL service account has necessary privilege to access the user desktop's database. If the requirement is ongong then using Upsizing wizard is not ideal (I think).

    If not make sure to copy the Access .MDB file to SQL server on daily basis, and run SQL DTS task to pickup the file to do necessary steps.

    HTH


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. simflex New Member

    hi Satya, not quite sure your suggestion will work.
    sql server is on a remote server while access db is on the user's desk top.
    So I am not how possible it will be to use dts on a remote server to extract data from someone's desktop.
    It's kind of like running a webserver on someone's pc.
    The only person who can view the code sitting on that webserver is the owner of the pc only unless you are viewing from his/desktop.
    But in my situation, I have figured out a way to extract data from the user's desktop by automating a macro.
    My problem right now is there are 5 access databases that are being moved to an excel spread sheet.
    Each database has 15 tables; that is 75 tables.
    So using dts, will only move one table at a time.
    This process will take forever.
    I was wondering if there is a script or something that will help move each database with its tables.
    So rather the process taking 75 steps, it will take 5 steps with each step moving 1 database with its 15 tables.
    Please advise, Satya and thanks again.
  10. satya Moderator

    I'm not familiar with your method of running macro and being this is a ongoing requirement, there should be a way to export access MDBs to the server and schedule the DTS job to import the data.

    As its on a desktop first of all you should sort out the issue of exporting MDBs data to the server and once its finished I don't see any issue with using DTS to migrate data for 75 tables.

    What is the size of access data you're managing?

    From Gaurav's referred KBA take a look at thishttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc97/html/acsqlres.asp page for any help.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page