June 2, 2012 at 11:27 pm
Dear All,
On our production server we have DTS job in which it transforms the records from one serve to another server.data is very huge thats why we choosen SSIS . but that job is failing with below error please help us regarding this
Date 02/06/2012 18:01:19
Log Job History (DTS)
Step ID 2
Server XXXXXXXXXXX
Job Name DTS
Step Name DTS
Duration 00:05:46
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 18:01:20
Error: 2012-05-28 18:07:02.77
Code: 0xC0047062
Source: Update of Watch Notices Script Component [51]
Description: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
End Error
Error: 2012-05-28 18:07:02.80
Code: 0xC0047022
Source: Update of Watch Notices SSIS.Pipeline
Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Script Component" (51) failed with error code 0x80131904 while processing input "Input 0" (53). 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.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 18:01:20
Finished: 18:07:03
Elapsed: 343.016 seconds
Any help much appreciated
Thanks in advance
:crying:
June 3, 2012 at 12:54 am
Hi,
In the .NET code you'll want to adjust the CommandTimeout property on the SqlCommand object being used to make the database call. The default is 30 (seconds). Setting it to 0 will allow the command to run indefinitely until the command completes.
If CommandTimeout setting does not help, then I would recommend checking query execution plan to see why it takes so much time to execute query. Maybe it is just not optimized indexes on database side or application uses less-than-efficient query.
Thanks& Regards
Ganesh N
June 3, 2012 at 8:09 am
Hi Ganesh,
Thanks for the response.
Actually we have 10 tables around 18millions rows average on each table. If we run that job after deleting all the rows its working fine.
If data is there its not working.
We have done with connection Timeout its not working.
I hope it will give better insight for the problem.:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply