Sql-server – SSIS/SSAS 2016 intermittent fault: An error was encountered in the transport layer

sql serversql-server-2016ssas-2016ssis-2016

I have an intermittent fault in my SSIS ETL process when I try and process my OLAP cube.

There are no error messages in the SQL log, and there's nothing else significant running at the time. The only error message I can find in the event log is:

An error was encountered in the transport layer.

SSIS and SSAS are SQL are all running locally on the same server, so I don't believe it's network related at all.

The same package has been deployed and has run successfully for weeks. If we manually connect to the cube it processes without any problem.

Windows Server 2012 R2 6.3 (Build 9600: ) on VMWare 64gb of RAM.

Microsoft SQL Server 2016 Enterprise (SP1-CU1) – 13.0.4411.0 SQL Max mem = 48gb

My gut feel is that this is more of an SSIS issue than a SSAS issue.

Has anyone else encountered this? Can anyone reccomend a solution or workaround? Is it possible to get additional debugging information?

Best Answer

I think we solved this or at least found a workaround.

I believe the problem is a timeout when connecting to SSAS when it has been inactive for some time. It has nothing to do with the actual processing of the cube.

We added a step to our ETL process which queried the OLAP cube before the processing step was triggered. This causes SSAS to 'wake up' before it is needed.

Since we added this step we haven't had a repeat of this problem.

Related Solutions

Sql-server – How to stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube

Sounds like you're getting orphaned fact rows a.k.a. early-arriving facts, meaning you have facts records that don't yet have a corresponding dimension member in one of the dimensions.

There's not really a way to solve this with the approach you're using because it's sounds like you'll always been a in situation where your dimensions are lagging your facts. But you can work around it by changing your dimension key error options in your fact processing. There is an option to ignore dimension keys not found. What you want to do is set that to "Ignore" and you might have to play around with some of the error limit options as well.

This should do the trick. Not the prettiest of solutions, but it will work and prevent these outages.

Sql-server – Kerberos Authentication Issue

Definitely sounds like a double hop (Kerberos) issue. When you're rdp'd into the box, then the cube is only 1 hop away so I'd guess that's why it works.

Start a profiler trace on the analysis services server, then query the linked server. Check the NTUsername field in the trace. If Kerberos Delegation is failing, the NTUsername will be something like "ANONYMOUS LOGON".

From Server1, what does the following return?

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

What Delegation permissions did you set up in AD for the machines?

Does the spn match the server name you are trying to connect to? If you have alias's, cnames, etc, you'll need an spn for each that you wish to connect to via kerberos.

Also, try out the Kerberos Configuration Manager to verify your settings. This can be particularly effective for reviewing what SPNs actually exist.

Best Answer

Related Solutions

Sql-server – How to stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube

Sql-server – Kerberos Authentication Issue

Related Question