?
Solved

DTS Transformation with Unique Ignore Dups Index Error

Posted on 2002-05-30
9
Medium Priority
?
726 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

764 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