SQL Server Agent Job intermittently running for packages on SSISDB

0
votes

I created an SSIS package to pull data from OLAP Cube and push it into SQL Server using SSIS 2012. I deployed the same on the SQL Server SSIS DB and created a SQL Server Agent Job to run the package. I have an account configured to run the job (not by creating proxy in the job, but SQL Server Agent is running under that account), that has access to the OLAP Cube. The job is running sometimes and failing sometimes.

Why the job behaving weirdly? Any help on the issue will help me a lot.

I am using SQL Server 2012 SP1 enterprise edition (11.0.3000.0) (if it helps). The error message which pops up when it fails is :

OLE DB Source failed the pre-execute phase and returned error code 0xC0202009

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: ""Microsoft OLE DB Provider for SQL Server 2012 Analysis Services."" Hresult: 0x00000001 Description: ""Error Code = 0x80040E05, External Code = 0x00000000:."". "

1
sql-serverssis

1 Answers

0
votes

I have had the same error on SQL2008 and now on SQL2012 SSIS, but have been able to eliminate / workaround.

We have a Loop Container in our Control flow that contains a data-flow task with an MDX source. The MDX query for the data-flow source is dynamically built (via an expression) on each iteration of the Loop container (however it always returns the "same shaped" results - only the filters in the WHERE clause are different).

We've found the error to be somewhat intermittent - sometimes the package will complete successfully, other times it will fail with the 0x80040E05 error at varying iterations thru the container loop.

To alleviate the problem we setup the SQL Agent job-step for this package to re-try on failure for up to 5 retries - not an ideal workaround, but it helped to improve the success rate of the Job.

We have no idea why this error is occurring or what is causing it, however it appears to be timing-related in some way and I have only seen the issue when using a SSAS OLE-DB data source with a dynamically generated MDX query. I have managed to virtually eliminate the error from occurring with a not ideal workaround in the SSIS package - no idea why this works/helps (hopefully Microsoft will be able to work it out and resolve the issue as it's been plaguing us since SQL2008 and is still here in SQL2012 SP1...

Workaround for MDX causing 0x80040E05 error:

Within our loop container we have added a Script task with OnSuccess precedent constraint to the data-flow task that contains the dynamically generated MDX source query. The script task simply introduces a WAIT in the processing immediately after the data-flow task completes of about 5 seconds, before allowing SSIS to continue with the next iteration (e.g. System.Threading.Thread.Sleep(5000)).

With this delay in place we have had much more stable SSIS package executions - dont know why, but that's what we have observed. Also note that when we migrated to SQL2012 SSIS packages the 0x80040E05 error returned, however we were able to eliminate it once more by increasing the WAIT time to 10 seconds on this script task.

Now waiting for 10 seconds is not an ideal solution / workaround to this problem - particularly when it is contained within a Loop Container (in our case it has added nearly 30 minutes of "WAIT time" to the package execution duration), however this workaround is better than having the package fail 80%+ of the time