Sql-server – SSIS package fails intermittently with: Unsafe assembly ‘microsoft.sqlserver.integrationservices.server, version=11.0.0.0

sql-server-2012ssis

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.

Best Answer

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.

Related Question