August 10, 2012 at 9:08 am
I am a rank noob when it comes to SSIS packages and I'm learning about them as fast as I can.
We have a particular SSIS package that runs every two hours during the business days. It works successfully most runs, but occasionally during our month-end close period, we'll observe that this package fails during one of its executions. I can click on the job and execute it again, right away and most times it will work correctly, however on Monday, it had to re-execute the package 3 times before we finally got a successful run out of it. I want to track down this issue to see if it can be fixed.
Here is the text (re-formatted for readability) of the package execution as copied out of the SQL Agent job history:
MessageExecuted as user: CIS\svc-sql03ssa.
Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved.
Started: 4:14:54 PM
Error: 2012-08-07 16:15:08.00
Code: 0xC0202009
Source: Insertion SO Tables -Staging DB IFCOLGX_SOHeader_SQL_DS [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: "Unspecified error".
End Error
Error: 2012-08-07 16:15:08.00
Code: 0xC004706B
Source: Insertion SO Tables -Staging DB SSIS.Pipeline
Description: "component "IFCOLGX_SOHeader_SQL_DS" (1)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Error: 2012-08-07 16:15:08.00
Code: 0xC004700C
Source: Insertion SO Tables -Staging DB SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2012-08-07 16:15:08.00
Code: 0xC0024107
Source: Insertion SO Tables -Staging DB
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 4:14:54 PM
Finished: 4:15:08 PM
Elapsed: 13.634 seconds.
The package execution failed.
The step failed.
The entire package contains only two steps - this failure was from the first step, but we've also seen it fail when executing the second step as well.
Some of the text in the error message points me to a particular dataflow which contains 4 different SELECT-transform-INSERT operations. It is extracting data from a CRM table and placing it into a staging database table. The following shows the T-SQL code from the OLE DB provider source:
SELECT
VW.*
FROM
VW_SOHEADER_IFCOLOGIXSL_FORMAT vw with(nolock)
left outer join IFCOLGX_SOHeader so with(nolock)
on vw.New_salesorderId = so.New_salesorderId
WHERE so.New_salesorderId IS NULL
Although the error message indicates "unspecified error" in the initial failure message, I have suspicions that it might be related to the fact that the query to fetch the data is being run against a view, which in turn, performs its select from a table in another database on the same server. That other table is based on a snapshot of the production CRM database. The snapshot gets taken every 5 minutes and I suspect that the intermittant nature of these errors are related to the process of a new snapshot being taken - possibly while the dataflow is grabbing records from the base table to plug into a staging table.
Is there a way that I can enable more detailed error condition logging related to this failure? Does anyone have any suggestions for me in how to track this down? Any assistance from readers would be greatly appreciated! Thanks!
Larry
August 10, 2012 at 9:18 am
Turn on the package logging ; there you can find multiple logging points. You can check package logging on msdn.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
August 10, 2012 at 9:34 am
I have faced a similar but not exact issue. In this the job used to fail on weekends. The job was with two different providers that works with last day trade data [+some business logic], but on Sunday's there was no data, so it fails.
This may be totally unrelated , but I mentioned as an FYI and May Be ..
*check out the package logging to get more idea about the error.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
August 10, 2012 at 1:08 pm
okay demonfox...
I've enabled package logging and selected a small handful of events to capture and have it running now. I've examined the dbo.sysssislog and ensured that it is writing records into the table and will post more up here when I see another failure. This may not happen until around the first week of next month (September) but I'll be keeping an eye on it to see if it captures a failure in the meantime.
...to be continued...
😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply