February 5, 2013 at 3:20 am
Hi
At last we ditch SS2000 and start new with SS2008R2 for our reporting database!
I have a re-written job package running which works 80% of the time on our development box.
The job copies yesterdays data from a application DB, makes analysis, aggregates and then inserts into reporting DB.
When it fails the log says...
[font="Courier New"]Executed as user: xxxxxx\xxxDEV043$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit ....
Started: 07:07:01
Error: 2013-02-05 07:08:38.55 Code: 0xC00291D7 Source: Execute SQL Task Execute SQL Task Description: No connection manager is specified.
End Error
Error: 2013-02-05 07:08:38.56 Code: 0xC0024107 Source: Execute SQL Task Description: There were errors during task validation.
End Error
Error: 2013-02-05 07:08:38.56 Code: 0xC0202009 Source: Get from CoOrdinator OLE DB Source [1] 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: "Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
End Error
Error: 2013-02-05 07:08:38.57 Code: 0xC00291D7 Source: Execute SQL Task Execute SQL Task Description: No connection manager is specified. End Error Error: 2013-02-05 07:08:38.57 Code: 0xC0024107 Source: Execute SQL Task Description: There were errors during task validation. End Error Error: 2013-02-05 07:08:38.57 Code: 0xC0047038 Source: Get from CoOrdinator SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. 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.
End Error
Error: 2013-02-05 07:08:38.60 Code: 0xC00291D7 Source: Execute SQL Task Execute SQL Task Description: No connection manager is specified.
End Error
Error: 2013-02-05 07:08:38.60 Code: 0xC0024107 Source: Execute SQL Task Description: There were errors during task validation.
End Error
Error: 2013-02-05 07:08:38.60 Code: 0xC002F210 Source: Update tblImportlog failure Execute SQL Task Description: Executing the query "UPDATE tblImportLog SET varInfo = 'Copy failed' ,d..." failed with the following error: "CoAvlsLogImport CoOrdinator copy failed". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 07:07:01 Finished: 07:08:38 Elapsed: 97.172 seconds. The package execution failed.[/font]
'No connection manager is specified.' makes no sense since 45 mins later the agent retry executes OK, looking back some days there are no retry just clean runs.
Nothing in Event log for sql.
Unfortunatly box also has a dev SharePoint 2010 on and this is throwing 'There is no default Access Services Application Proxy.' but not at the same times as my issue.
What on earth could I be looking for?
Best regards
Dave
February 5, 2013 at 3:35 am
There is more than just a 'can't find connection manager' error in there. There is also:
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
This is more likely the reason for your package to be failing intermittently.
So do some research on identifying and resolving deadlocks.
With regards to your connection manager message, perhaps you are loading connection manager properties dynamically (i.e. using a for each loop or configuration) and this has failed?
Regardless troubleshoot your deadlock issue first and perhaps your other one will go away.
February 5, 2013 at 3:51 am
What is expected from forum readers Dave from your error log?
February 5, 2013 at 4:03 am
smamidala (2/5/2013)
What is expected from forum readers Dave from your error log?
Hi smamidala
Simply, I'm looking for a clue as to where to investigate this issue. I homed in on the 'no connection manager' since this happened first... but that makes no sense as the job retry ran ok. I had dismissed the later reported deadlock error but now I'm advised I should look to that area.
Dave
February 5, 2013 at 4:26 am
smamidala (2/5/2013)
What is expected from forum readers Dave from your error log?
Hi smamidala
Simply, I'm looking for a clue as to where to investigate this issue. I homed in on the 'no connection manager' since this happened first... but that makes no sense as the job retry ran ok. I had dismissed the later reported deadlock error but now I'm advised I should look to that area.
Dave
February 5, 2013 at 4:57 am
Hi Dave,
Make sure you have all permission and check for the following resolutions.
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Please refer to more error codes from the following link
http://msdn.microsoft.com/en-us/library/ms345164.aspx
Thanks,
Sridhar.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply