SQL Server Performance

DTS

Discussion in 'SQL Server DTS-Related Questions' started by bensta, Jul 25, 2003.

  1. bensta New Member

    I am really new to DTS. I am trying to export data from an access database to a SQL Serve 7.0 database. I am trying to import data from tables one table at the time. I am using query within the data transformation wizard. I followed all the steps. I made sure that destination fields map with source fields under the transformation sercion. It is not importing data. FK constaint erros messages each time. What is tha I am doing wrong? What is the easiest way? If my relationships for my tables are defined in my SQL Serve Database could be the reason why I am getting problems with imports?
    Thank you for your help in advance

    Tarik Bensehil
  2. gaurav_bindlish New Member

    When you insert data into SQL Server tables bound by Foreign key constraints, you have to insert the data in the dependent tables first. This means if A is dependent on B, you have to insert in B first and then A.

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

    Thank you very mcuh. I really appreciate your help.
    If I may, is there any way could populate tables without doing it one table at the time without having to worry about foriegn keys constraints?
    Thank you and have a wonderful weekend
    Bensta

    Tarik Bensehil
  4. gaurav_bindlish New Member

    You'll have to drop all the foreign key / Check constraints from the table for doing so. Also all the triggers need to be disabled. I would not recommend this option.

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

    I followed your instructions, but I am still having errors messages poping up.
    I have my relationships between my tables defined in SQl Server Database. The tables have no records.
    I want to import the data from an existing database that is stored in an Access mdb file.
    The access database is not very well normilized. I had to write a set of queries to create foreigh keys so that the tables map with the corresponding tables in the SQL Serve Database. I also had to update some records, but I ended up with empty records since they were not stored due to poor normalization. Thus I have some orphened records in the access database.
    When I do the import do I have to remove the relationships defined in SQL Database first?
    And when you say "When you insert data into SQL Server tables bound by Foreign key constraints, you have to insert the data in the dependent tables first. This means if A is dependent on B, you have to insert in B first and then A." What if I have a lot of dependent tables,such as lookup tables? How can handle it?
    Thank you so much for your help. I appreciate a lot
    I also hope I was clear with my questions



    Tarik Bensehil
  6. gaurav_bindlish New Member

    I would say u need to look into your design to find all the parent child relationships and after that insert data in the parent and then child and subsequent parent and child...

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

    Thank you very much for your input gaurav_bindlish.
    what if the Check existing data on creation if checked, will have into problems when importing data if some records do not exist in the foreing key tables?
    Thank you again

    Tarik Bensehil
  8. gaurav_bindlish New Member

    I think that option is set for the data already present in the tables and not the data to be imported.

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

    Shoul I leave that option unchecked for the moment. And then check it once I am done importing the datat?

    Tarik Bensehil
  10. gaurav_bindlish New Member

    Isn't that option specific for a DTS package? If it is, is doesn't matter once the packaage has executed.

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

Share This Page