Solved

DTS Transformation with Unique Ignore Dups Index Error

Posted on 2002-05-30
9
724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 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