locked
Cannot Avoid Duplicate records RRS feed

  • Question

  • Hi

     

    I have SSIS packages which get unique jobname from batchrecords_temp table and insert into batchrecords table.

    I Have a stored procedure which runs in an SSIS package, allows only unique jobnames , Here is the syntax of my stored procedure

    select x.* from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
    left join BatchRecords j ON j.BatchRecordID = x.BatchRecordID
    where j.JobName is null and x.Rownum = 1 

    When i execute the above query i get unique jobnames , but when it is run in an SSIS package my job fails ,

    I get an error below, i cannot understand how can i get unique values when i run the query and why do i get this error  while in SSIS package..

    i have an unique  index  on Jobname in batchrecord table, and batchrecord_id is the primary key

    Batchrecords_temp does not have any keys i just use it as a staging table in the package.

    Please Help

     

     

    [OLE DB Destination [115]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot insert duplicate key row in object 'dbo.BatchRecords' with unique index 'JobName'.".

     

     

     

    Monday, June 14, 2010 6:26 PM

Answers

  • What does it return

    SELECT * FROM batchrecords

    WHERE EXISTS (SELECT * FROM batchrecords_Temp T

    WHERE batchrecords .batchrecord=T.batchrecord)

    • Proposed as answer by Tom Li - MSFT Thursday, June 17, 2010 1:55 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 23, 2010 12:38 PM
    Tuesday, June 15, 2010 5:49 AM
    Answerer

All replies

  • What does it return

    SELECT * FROM batchrecords

    WHERE EXISTS (SELECT * FROM batchrecords_Temp T

    WHERE batchrecords .batchrecord=T.batchrecord)

    • Proposed as answer by Tom Li - MSFT Thursday, June 17, 2010 1:55 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 23, 2010 12:38 PM
    Tuesday, June 15, 2010 5:49 AM
    Answerer
  • What does it return

    SELECT * FROM batchrecords

    WHERE EXISTS (SELECT * FROM batchrecords_Temp T

    WHERE batchrecords .batchrecord=T.batchrecord)

    Tuesday, June 15, 2010 5:49 AM
    Answerer