SSIS package fails - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SSIS package fails

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-14 : 14:28:50

Hi

I have SSIS packages which get unique jobnames 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.

Any Suggestions are highly appreciated ,

Please Help

Here is the Error:

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 15:16:53

Somewhere the code is trying to insert more data into table dbo.BatchRecords, and that's when the code fail.

N 56°04'39.26"
E 12°55'05.63"

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-14 : 16:05:08

Do you think there is anything wrong with syntax above , that allows only one record right?

Could you please suggest me something , if i need to check anything ? properties anywhere etc?

quote:
Originally posted by Peso

Somewhere the code is trying to insert more data into table dbo.BatchRecords, and that's when the code fail.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources