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'.".
