Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 12th, 2004, 05:16 PM
Registered User
 
Join Date: Nov 2004
Location: Sycamore, IL, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Handle key violations in transfering data.

I'm a newbie. Relatively comfortable with Access/jet but learning SQL Server.

How do I handle importing or transferring rows with duplicates in the primary key columns?

In an Access Project a simple Append stored procedure just stops when it gets to the first row containing duplicates, as you know. No rows are transferred to my target table. Same result when using SQL Query Analyzer and INSERT INTO target SELECT * FROM source. Just stops. Both give error message about dups in primary key (I think).

I've also tried an INSTEAD OF trigger on my target table. After various tests with this I've inferred that the INSTEAD OF trigger fires only one time and not for each incoming row, as I would want. (BTW, Books Online for INSTEAD OF suggest it should work in handling key violations, but BO is unclear if that means a batch of rows or just one.)

I've had qualified success with DTS and adjusting some properties in the Transfer Data Task Properties dialog. I specified an Exception file. I unchecked 7.0 Format, checked Error Text, Source error rows, and Dest error rows boxes. Set the Max error count value to cover the number of duplicates, and set the Insert batch size value to 1. This seems to work and does create the Exception file listing the row numbers that had primary key duplicates. BUT it doesn't create either the Exceptionfile.txt.source or Exceptionfile.txt.target files. I was hoping for a text file containing the rows not transferred.

My questions are:
1. How do I get the Exceptionfile.txt.source and Exceptionfile.txt.target files to be created?
2. Am I correct that an INSTEAD OF trigger is not the way to go?
3. Is there another, different, strategy that I should consider?

Thanks.

Jerry



  #2 (permalink)  
Old November 13th, 2004, 09:20 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

In your transformation task portion select the tab 'option' and under exception file place or browse to the path of a previous named file. for example: 'Exception.txt'. any exception created will be store in this file with all needed information.

Jaime E. Maccou
Applications Analyst
  #3 (permalink)  
Old November 15th, 2004, 11:11 AM
Registered User
 
Join Date: Nov 2004
Location: Sycamore, IL, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the input, Jaime. I've included below the basics from the Exception.txt file.

-----------------
DATA TRANSFORMATION SERVICES: Data Pump Exception Log


Package Name: AppendFrom APPFIXEDToAPPFIXEDtbl
Package Description: DTS package description
Package ID: {1117D2F3-1754-4245-934C-2C9D60F93057}
Package Version: {E1F2EFE6-6157-4CF7-891F-5E7CA34DE4BA}
Step Name: Copy Data from APPFIXED to [Jerrys Second SQL Database].[dbo].[APPFIXED] Step

@@ExecutionStarted

Execution Started: 11/15/2004 9:44:58 AM

@@LogSourceRows: C:\Documents and Settings\My Documents\Junk\APPFIXEDImportExceptions.txt.Source
@@LogDestRows: C:\Documents and Settings\My Documents\Junk\APPFIXEDImportExceptions.txt.Dest

@@ErrorRow: 1
Error at Destination for Row number 1. Errors encountered so far in this task: 1.

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:The statement has been terminated.
Error Help File:
Error Help Context ID:0


Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Violation of PRIMARY KEY constraint 'PK_APPFIXED2'. Cannot insert duplicate key in object 'APPFIXED'.
Error Help File:
Error Help Context ID:0
@@SourceRow: Not Available
@@DestRow: Not Available
-------------------


As you can see, the only information provided about the exception rows, other than the row number, is : @@SourceRow: Not Available. Same for DestRow.

This is contrary to Chaffin, Knight and Robinson in (Wrox Press) Professional SQL Server 2000 DTS, page 184 (bottom).
I was hoping to see the actual exception rows.

I'm also hoping I can get some help with my basic questions:
1. How do I get the Exceptionfile.txt.source and Exceptionfile.txt.dest files to be created?
2. Am I correct that an INSTEAD OF trigger is not the way to go?
3. Is there another, different, strategy that I should consider?

Thanks, everybody.

(BTW, in Enterprise Manager, when I go to menu Help and click on About Microsoft Management Console, I see the following:
"Microsoft Management Console 2.0
Version 5.1 (Build 2600.xpsp_sp2_rtm.040803-2158: Service Pack 2)
Copyright 1981-2001 Microsoft Corporation."
Could this be a patch issue??)

Thanks again.

Jerry

 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Php transfering data to mysql Gaurava PHP How-To 2 January 5th, 2008 06:33 AM
Amount of Data Access can handle bright_mulenga Access 2 February 2nd, 2007 07:54 AM
Transfering data from csv file to data base g_vamsi_krish ASP.NET 1.0 and 1.1 Professional 2 May 16th, 2006 11:58 PM
Creating and transfering data to a new form... Brettvan1 VB.NET 2002/2003 Basics 5 December 9th, 2004 04:16 AM
Transfering data from one machine to another..... nikosdra SQL Server 2000 0 September 6th, 2003 05:08 AM



All times are GMT -4. The time now is 12:38 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.