title: DTS Package Terminates because of Duplicate Key Row
body: <p>We have an old DTS Package that our SQL 2000 Server uses to push Employee records out to machines on our manufacturing floor.</p>
  
  <p>Recently, we upgraded one of the machines, and it now is running SQL 2008 Express.</p>
  
  <p>We have reconfigured the DTS Package to push the Employee records out to this new Server, but now we are getting this error message:</p>
  
  <p><strong>FETCH_EMPLOYEES:</strong>  </p>
  
  <blockquote>
    <p>The statement has been terminated. Cannot insert duplicate key row in object 'dbo.Users' with unique index 'IX_tblUsers_OpID'.</p>
  </blockquote>
  
  <p><img src="https://i.stack.imgur.com/8dVlH.png" alt="screenshot"></p>
  
  <p>If I remote into our SQL 2000 Server, I can Right-Click to execute each step of the DTS Package in succession with NO errors.</p>
  
  <p><img src="https://i.stack.imgur.com/IfCjj.png" alt="screenshot"></p>
  
  <p>So, I log onto this machine's SQL 2008 Express instance to see if I can figure anything out.</p>
  
  <p>Now I am looking at the <strong>FETCH_EMPLOYEES</strong> stored procedure:</p>
  
  <pre><code>PROCEDURE [dbo].[FETCH_EMPLOYEES] AS
  DECLARE @OpID varchar(255)
  DECLARE @Password varchar(50)
  DECLARE Employee_Cursor CURSOR FOR
  SELECT OpID, Password
  FROM dbo.vw_Employees
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor
  INTO @OpID,@Password
  WHILE @@FETCH_STATUS = 0
  BEGIN
  insert into dbo.Users (OpID,Password,GroupID) 
  VALUES (@OpID,@Password,'GROUP01')
  FETCH NEXT FROM Employee_Cursor
  INTO @OpID,@Password
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor
  </code></pre>
  
  <p>I don't really understand Cursors, but I can tell that the data is being pulled from a view called <code>vw_Employees</code> and being inserted into the table <code>dbo.Users</code>.</p>
  
  <p>The view <code>vw_Employees</code> is simple:</p>
  
  <pre><code>SELECT DISTINCT FirstName + ' ' + LastName AS OpID, Num AS Password
  FROM         dbo.EmployeeInfo
  WHERE     (Num IS NOT NULL) AND (FirstName IS NOT NULL)
        AND (LastName IS NOT NULL) AND (Train IS NULL OR Train &lt;&gt; 'EX')
  </code></pre>
  
  <p>So, now it seems the problem must be from the table <code>dbo.Users</code>.</p>
  
  <p><img src="https://i.stack.imgur.com/mrevX.png" alt="screenshot"></p>
  
  <p>I did not see anything particularly attention getting with this, so I scripted this table using a <code>CREATE TO Query Editor</code> and got this information that I don't really understand:</p>
  
  <pre><code>CREATE TABLE [dbo].[Users](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [OpID] [nvarchar](255) NOT NULL,
  [Password] [nvarchar](50) NOT NULL,
  [GroupID] [nvarchar](10) NOT NULL,
  [IsLocked] [bit] NOT NULL,
      CONSTRAINT [PK_tblUsers] PRIMARY KEY CLUSTERED 
  (
  [ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  
  ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_tblUsers_tblGroups] FOREIGN KEY([GroupID])
  REFERENCES [dbo].[Groups] ([GroupID])
  GO
  
  ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_tblUsers_tblGroups]
  GO
  
  ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_tblUsers_IsLocked]  DEFAULT ((0)) FOR [IsLocked]
  GO
  </code></pre>
  
  <p>OK, I feel the problem is somewhere in this table definition, but I don't really understand what it is doing (after creating the basic table).</p>
  
  <p>It has a CONSTRAINT section with lots of variables I do not understand, then it is altering these tables to add FOREIGN KEY and CONSTRAINTS.</p>
  
  <p>My Question: Could someone help me understand what the error is telling me (other than there is some duplicate key violation).</p>
  
  <p>What column could be throwing a duplicate key violation?</p>
  
  <p>Did I include enough data and screenshots?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Based on Comments, it sounds like this screenshot is needed.</p>
  
  <p>In the <strong>Users</strong> table, there is a list of Indexes, and one called <code>IX_tblUsers_OpID</code> says it is Unique and Non-Clustered.</p>
  
  <p>I think we have eliminated duplicate <code>Op_ID</code> values on our source data table <code>EmployeeInfo</code> by finding all of them with this script:</p>
  
  <pre><code>select num as 'Op_ID', count(num) as 'Occurrences'
  from employeeInfo
  group by num
  having 1&lt;count(num);
  </code></pre>
  
  <p>This <em>should</em> have gotten rid of all of my duplicates. Right?</p>
  
  <p>We purchase manufacturing machines that come configured with PCs for storing local data. They supply these script I have posted up, so I cannot comment on why they picked what they did. We just run a job that pulls the data onto our server.</p>
  
  <p><img src="https://i.stack.imgur.com/KpAYj.png" alt="screenshot"></p>