Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: Exclude duplicates in DTS package

    Using SQL Server 2008 Express.

    In the past, I've made DTS packages for MSDE (SQL2000). Looking for some guidance on the following project:

    Need to develop a weekly process to update a specific column in a specific table in a SQL 2008 Express database. The column contains full Employee Names. Destination field is called PL5 and is PrimaryKey and it does not accept nulls or duplicates.

    Source: XLS or MDB. Columname is "Fullname"

    Destination Database Name: Personnel
    Table Name: PL5
    Column Name: Item (PK, varchar, no nulls)

    Using DTS:

    1. Despite finding out that you cannot save DTS packages in SQL 2008 express, I got it to work with the DTS install that was made for SQL2000. In SQL Server Mgt Studio under Legacy DTS, I can see and run the saved packages.
    2. Can get FullName field from XLS spreasheet.
    3. Excluded nulls by using "Fullname<>null"
    3. Tried to insert that data to a column in a specific table. However, I get errors that indicate no duplicates.

    Question: Is there a statement that can be used in DTS to compare the XLS to the target column so that only NEW records are transferred?

    Maybe I should look at Stored Procedures?

    Helpful comments appreciated. Thanks.

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    It seems fairly easy task to resolve.
    1. Import the data into temp table ie Personnel_Temp allow duplicate null etc.
    2. After import, check on duplicates and nulls then remove them in Personnel_Temp.
    3. Now join the tables Personnel and Personnel_Temp as below to find only the new records.
    SELECT a.*
    FROM Personnel_Temp a
    Left Join Personnel b
    ON a.FullName = b.FullName
    WHERE b.FullName IS NULL
    4. Insert the records in #3 into Personnel.
    INSERT INTO Personnel
    SELECT a.*
    FROM Personnel_Temp a
    Left Join Personnel b
    ON a.FullName = b.FullName
    WHERE b.FullName IS NULL

    All these tasks can be done in "Execute SQL Task" in DTS.
    Last edited by TerryP; 02-08-10 at 21:44.

  3. #3
    Join Date
    Feb 2010
    Posts
    4
    That makes sense. I'll give it a whirl. Thanks Terry!

Posting Permissions

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