[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

DTS Transformation with Unique Ignore Dups Index Error

Posted on 2002-05-30
9
Medium Priority
?
728 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 and will be exposed to the many uses the SELECT statement has.

650 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