November 29, 2013 at 1:59 am
Hi Guys,
I have a job which run from Data collector and failed with this error message Name of the job is collection_set_3_upload .
Component name: DFT - Upload collection snapshot, Code: -1071636471, Subcomponent: ODS - Upload snapshots into active_sessions_and_requests table [2686], Description: 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: "Violation of PRIMARY KEY constraint 'PK_active_sessions_and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (949325, 2013-10-27 02:38:27.8930000 +02:00, 1).". . SSIS error. Component name: DFT - Upload collection snapshot, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ODS - Upload snapshots into active_sessions_and_requests table" (2686) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (2699). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. . NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.
Please may you help with this impossible task?
BR,
Hadrian
November 29, 2013 at 7:42 am
Hadrian (11/29/2013)
Hi Guys,I have a job which run from Data collector and failed with this error message Name of the job is collection_set_3_upload .
Component name: DFT - Upload collection snapshot, Code: -1071636471, Subcomponent: ODS - Upload snapshots into active_sessions_and_requests table [2686], Description: 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: "Violation of PRIMARY KEY constraint 'PK_active_sessions_and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (949325, 2013-10-27 02:38:27.8930000 +02:00, 1).". . SSIS error. Component name: DFT - Upload collection snapshot, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ODS - Upload snapshots into active_sessions_and_requests table" (2686) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (2699). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. . NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.
Please may you help with this impossible task?
BR,
Hadrian
Most of what you need to know is in the error message:
1. The SSIS package "Upload collection snapshot" is failing
2. The task within the package that is failing is "ODS - Upload snapshots into active_sessions_and_requests table "
3. The source of the failure is trying to insert data that violates the primary key "Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (949325, 2013-10-27 02:38:27.8930000 +02:00, 1)"
So you will need to open the package (point 1) in BIDS, investigate what the task (point 2) is doing, I suspect its a data flow task.
Identify the SQL Query that is being ran to insert the data into the destination table "snapshots.active_sessions_and_requests".
from there build a query identifying the rows that are in your source query that alredy exists at the destination.
Without the package I can't be anymore specific than that I'm afraid.
MCITP SQL 2005, MCSA SQL 2012
December 2, 2013 at 1:31 am
Hi,
Indeed before posting I have checked the table in msdb snapshot_active_requests for duplicates and found 0 and the row incriminated was not here.I susupect that is a bug ,because job run well two days and suddenly failed again with the same error.
October 27, 2014 at 3:24 am
I had the same issue. Resolved it by:
- Stop collection job
- remove all files with "QueryActivity" in the name from \\%servername%\c$\Users\%Account used by collection job%\AppData\Local\Temp
- Start collection job
November 2, 2015 at 2:22 pm
Thanks for the post. This worked for me also and I'm OK with the data loss.
November 15, 2015 at 11:32 pm
Hi all,
I have done with deleting query activity from temp folder.but issue came again after few days.
can anyone help me on this.
reason of issue seems to be daylight saving setting on server got changed.
I also took reference from below link.
https://connect.microsoft.com/SQLServer/feedback/details/1946994
Executed as user: Workgroup\50009-DB2$. SSIS error. Component name: DFT - Upload collection snapshot, Code: -1071636471, Subcomponent: ODS - Upload snapshots into active_sessions_and_requests table [2686], Description: 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: "Violation of PRIMARY KEY constraint 'PK_active_sessions_and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (14044, 2015-11-01 01:46:26.0100000 -05:00, 1).". .SSIS error. Component name: DFT - Upload collection snapshot, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ODS - Upload snapshots into active_sessions_and_requests table" (2686) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (2699). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.
November 7, 2019 at 5:29 pm
Just check where the collection files are being dumped and remove the file with the date stamp in the error message:
2013-10-27 02:38:27.8930000 +02:00, 1
Re-run the job, it should work. To check where files are being saved, check syscollector_config_store_internal in MSDB.
November 19, 2019 at 3:10 pm
From my experience this is not necessary.
Please try the below first to avoid data loss.
- Stop collection job
- Remove the oldest file with "QueryActivity" in the name from \\%servername%\c$\Users\%Account used by collection job%\AppData\Local\Temp
- Start collection job
-Repeat until fixed
When you start the job again after doing this the other cache files should be uploaded and no data lost.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply