We are using a datareader component to retrieve data from a Pervasive 8.6 database. We have four separate datareader components in various packages retrieving data into our datawarehouse in SQL2005. One of the components has started to fail regularly with the following error.
Date 6/8/2007 3:05:00 AM
Log Job History (LoadMAXDailyBookings)
Step ID 1
Server US-CO-DEN-101
Job Name LoadMAXDailyBookings
Step Name Load Bookings Step
Duration 00:00:37
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Destination Write Bookings Detail" (121)" wrote 0 rows.
End Info
Log:
Name: PipelineBufferLeak
Computer: US-CO-DEN-101
Message: component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
End Log
Log:
Name: OnTaskFailed
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnPostExecute
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnWarning
Computer: US-CO-DEN-101
Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Log
Warning: 2007-06-08 03:05:36.92
Code: 0x80019002
Source: LoadMAXDailyBookings
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution...
The other components run without any problems as did this one up until we installed service pack 2. We then started getting these occasional failures. Any thoughts on what is happening here?
Thanks,
Phil
Do you have package logging turned on? The PipelineBufferLeak, I believe, is a warning, not an error, and hence would not stop execution.Turning on package logging might help get a more clear error message....|||
I do have logging turned on and the error in the log is more vague than within the job history. I'm copying in the first rows indicating the error from the log. The execution is halted due to whatever error is happening.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookings,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
|||Make a copy of that package and strip everything out of the data flow except for the data reader source. Then hook it into a row count transformation. Re-run the package. What happens?|||The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.|||
pkdenver wrote:
The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.
The only reason I suggested this is because the error message returned is a DataReader failure message, so this takes out all processing that might unduly influence things. Can you use something other than a DataReader Source? OLE DB Source, for instance?|||
I see your point regarding isolating the datareader. As recommended last week, I deployed a package which performs the query and dumps to a rowcounter. We get the same failure as the regular job. Both jobs failed on one day (Sat). It ran fine on Sun and Mon. Here are the error rows from the logger: I'm wondering if we are getting a timeout on this query as it is a more complex query than the other three jobs running against the DB.
Regarding an alternate driver, the Pervasive OLE DB driver is very unstable in the SSIS environment and so it can't be used at all. We are looking at the timeout value to see if the Pervasive ODBC driver can be tweaked.
OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016268,0x,Execute phase is beginning.
OnPipelinePrePrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,PrimeOutput will be called on a component. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
OnPipelinePostPrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,A component has returned from its PrimeOutput call. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.
OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,50,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Post Execute
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.
OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,50,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Cleanup
PipelineBufferLeak,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
OnTaskFailed,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnPostExecute,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnWarning,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
OnPostExecute,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
PackageEnd,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1,0x,End of package execution.
