?
Solved

DTS Transformation with Unique Ignore Dups Index Error

Posted on 2002-05-30
9
Medium Priority
?
729 Views
Last Modified: 2013-11-30
I have a basic transformation on a DTS package that copies data from one table to another. I want it to ignore duplicates for a specific column so I created a unique index with ignore duplicates on the column that I want unique. When I run the package and I encounter duplicate values and they are ignored by the index the DTS package indicates that an error was encountered even though it copied the rows correctly. How can I get it to run and ignore the duplicates but not report an error?
0
Comment
Question by:JamesT
9 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 7045448
Hi,

Have you tried using the distinct clause in your select?

As in

select distinct col1, col2
from table1 ...

That would eliminate the need to use the index that is causing the error.

Regards
  David
0
 
LVL 2

Author Comment

by:JamesT
ID: 7045454
Unfortunately for this using the distinct clause won't work.
0
 
LVL 6

Accepted Solution

by:
curtis591 earned 400 total points
ID: 7045525
I am not sure how to solve your specific problem but when working with DTS packages I always copy my records into a temporary table first.  This gives me the ability to filter and clean any data that I have to before I insert my records into my master table.  I find it just keeps my package simple and straight forward and it is easy for somebody else to take a look at it.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 5

Expert Comment

by:spcmnspff
ID: 7047180
Are the two tables on the same server?
0
 
LVL 2

Author Comment

by:JamesT
ID: 7047198
One table is a FoxPro table while the destination is a SQL Server 2000 table. It just seems to me that to report an error for an index that has an ignore duplicates clause on it is wrong.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7047250
Well the problem is, we have no way of really trapping the error from within DTS.  I would suggest setting up a linked server on you sql server that uses OLEDB/ODBC to connect to your fox pro file.

1.)Set up a system dsn on you server pointing to your foxpro file.

2.) Set up a linked server on your sql server using the microsoft oledb for odbc drivers and enter in the system dsn that you just created.

Now instead of a DTS package you can create a proc that does an insert based on an outer join on the indexed field. And schedule a job to peform the task:

3.)Insert into LocalSQLTable
(UniquelyIndexedField, Field1, Field2, . . .)
Select UniquelyIndexedField, Field1, Field2, . . .
From LinkedServer...TableName T1 Left Join LocalSQLTable T2
    ON T1.UniquelyIndexedField = T2.UniquelyIndexedField
WHERE T@.UniquelyIndexedField Is Null


0
 
LVL 1

Expert Comment

by:crw030
ID: 7050359
JamesT.

Try this (didnt have a FoxPro database but it works SQL to SQL)

In your DTS package:  Create COnnection 1 (Foxpro) & Connection2 (SQL)

Create a "Transform Data Task" with the Foxpro database as the source and the SQL database as the target (if that's what you need)

Select PROPERTIES for the Transform Data Task..Go to the OPTIONS tab. CHECK "enable fast load" & "Enable IDENTITY INSERT"

Goto the SOURCE TAB and change it from Table(default) to SQL.  Your SQL needs to be written to remove the duplicates like:
SELECT  Deskcalls.[ID],Deskcalls.[Name],Deskcalls.[email]
FROM  Deskcalls
WHERE (id NOT IN (SELECT ID FROM MAILLISTS))
(Be sure to pick the database & table for your lookup table (in my example MAILLIST) from the correct connection.  Double check your Transformations.  It should work.  (on my SQL to SQL it will insert whichever rows do not already have a matching ID in the destination table)  This prevents the failure indication (I think) so the rest of your DTS will run...having never seen the failure
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7050973
James in your query

SELECT  Deskcalls.[ID],Deskcalls.[Name],Deskcalls.[email]
FROM  Deskcalls
WHERE (id NOT IN (SELECT ID FROM MAILLISTS))

foxpro table and maillists wDeskCalls would be the ould be the sql table?  How could you write a query that selects from both servers without a linked server?
0
 
LVL 2

Author Comment

by:JamesT
ID: 7053858
Although this didn't resolve the issue the way I wanted it to it will resolve the overall problem. Thanks to all that helped.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question