SQL Server Performance

DTS Import Question

Discussion in 'SQL Server DTS-Related Questions' started by MadCat_SQL, Jun 6, 2005.

  1. MadCat_SQL New Member

    Hi all,

    I'm relatively new to sql server so please bear with me.

    At present i have a large number of Access databases consisting of the same tables (different data). I want to merge a couple of these tables so that they are stored into one table on SQL Server 2000.
    I was going to write an app in VB.net to do this but then thought that it may be better to use the DTS feature.

    Is it possible to import from multiple sources into the one destination. i.e. loop through the Access DB's and import data from just the table named [PMI] into a PMI table already created on the SQL server? If so, how would i go about doing this?

    Any help on this matter would be most appreciated.

    Thanks in advance

    Grant
  2. satya Moderator

    I think it is not possible to import from various MDBs, as you need to specify one .mdb file at a time. What you can do is create individual tables and use the corresponding Access dbs to import.
    For the ease of operation simply follow the DTS wizard option using SQL enterprise manager --> Tools --> Wizards.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. MadCat_SQL New Member

    Thanks for the quick response.

    Is there not anyway to create a script that loops through the databases and can call a DTS package with a variable source database which is changed dynamically?

    May just have to go back to looking at the VB.Net route.

    Thanks for your help.

    Grant
  4. dineshasanka Moderator

    I had this issue
    As Satya said you will not be able to create DTS for several MSAccess. However, you can save the DTS into VB Script and change it. But take my word IT IS VERY Difficult.
    I think best option is to create vb application with out a DTS
  5. MadCat_SQL New Member

    dineshasanka,

    Thanks for the advice, i had just come across a similar post on another fourm about altering the DTS code. It looks like it would be far simpler to write a quick program to do what is required.

    Thanks for your help.
  6. dineshasanka Moderator

    Great, can you paste the link please
  7. MadCat_SQL New Member

    Dineshasanka,

    My mistake, it was on this forum. It doesn't mention about loops but does mention parameterizing the VBcode. I seen this and wondered about altering the vb code to incorporate a loop for my purposes.

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8147

    apologies if my previous post was a bit misleading and this isn't of any use to you.

    Thanks again.
  8. Raj1979 New Member

    Am I missing something on this. We should be able to create a DTS for this particulat task. You should be able to create different connection cans and creating one destination can. Meaning if there are two source access databases then create two connections one for each source. Then you will have destination as one connection. Between these trhee connections you should be able to create transformation tasks.

    Friends, correct me if I am wrong or if I am out of track

Share This Page