3

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job.

Now it fails intermittently with the following error:

SQL agent history says says:

Date 6/12/2015 6:00:00 a.m. Log Job History (PBI) Step ID 1 Server DEV-DW Job Name PBI Step Name Customer DETL Duration 00:00:12 Sql Severity 16 Sql Message ID 27195 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: \. The operation failed because the execution timed out. [SQLSTATE 42000] (Error 27195). The step failed.

In the SQL logs it says:

Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=xxxxxxxxxxxx, processorarchitecture=msil' loaded into appdomain 73 (SSISDB.dbo[runtime].103).

  1. Why would it only fail sometimes?
  2. Why is it failing now?
  3. What is the root cause?
  4. How can I fix this?

Running SQL 2012 SP2 (11.0.5343) on Win 2008 R2 Standard SP1 on VMWare.

Edit: Updated to SQL 2012 SP3 (11.0.6020).

Edit: have added more context and clarified.

Following suggestion from @TheGameisWar, I queried ssisdb

SELECT * FROM [SSISDB].[catalog].[event_messages] WHERE event_name = 'OnError'

Returns no error messages for that date.

Improve this question
6
  • What else has happened on the box besides patching from SP2 to SP3? Failover in a cluster? New DBA/team administering the box? Virus scanner installed/turned on?
    – billinkc
    Dec 7, 2015 at 2:46
  • No other changes. The few cryptic responses i found when searching for the error referred to hotfixes and CU's even though they seem vaguely related. I thought I should ensure I was on current version before pleading for help. (even if there was low probability the update resolves the issue). Task is scheduled to run overnight will confirm tomorrow if it has resolved issue.
    – Sir Swears-a-lot
    Dec 7, 2015 at 5:43
  • I think I remember seeing this after someone's done a restore of the SSISDB poorly, or they've changed user/owner, etc. Patches rarely hurt though so won't knock you on that point
    – billinkc
    Dec 7, 2015 at 5:46
  • Task owner should be sa. Will confirm tomorrow. But that shouldn't have changed over the weekend when it occurred last.
    – Sir Swears-a-lot
    Dec 7, 2015 at 5:48
  • 1
    That is not the error,unloading an (assembly in sql is loaded outside buffer pool memory as clr component) is due to memory pressure,you can query the exact error by querying ssisdb.catalog.event_messages where event_name=onerror'
    – TheGameiswar
    Dec 7, 2015 at 13:23

1 Answer 1

Reset to default
1

This has now run successfully for ~1.5 weeks. The more I think about it I believe that the ssis error (posted in title) was a side effect, and was the result of the timeout caused by the dataload connection.

I can't prove or disprove if installing SP3 had any effect at all.

In my opinion the comment from @TheGameiswar is the best indicator pointing to root cause.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.