Solved

DTS Transformation with Unique Ignore Dups Index Error

Posted on 2002-05-30
9
714 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 100 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
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7047180
Are the two tables on the same server?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

939 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now