Symptoms

Consider the following scenario:

  • You use Microsoft SQL Server 2012 or SQL Server 2014 and Microsoft SQL Server Data Tools (SSDT) to design a SQL Server Integration Services (SSIS) Package.

  • In the Dataflow Task, you use a source component such as OLE DB source, ADO.NET source, or ODBC source, to set the Data Access mode to SQL Command and type a query in the command box.

  • You click Preview to see the result of the query.

In this scenario, the command that is previewed runs longer than 30 seconds and fails in the designer. Additionally, you receive an error resembles "Query Timeout Expired."

Cause

The issue occurs because the CommandTimeout threshold is hard coded as 30 seconds for the preview feature in SSIS designer.

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Notes:

  • Before you apply this hotfix, the time out settings is fixed to 30 seconds in data preview for all SSIS components.

  • After you apply this hotfix:

    • For ADO.NET source component and OLE DB source component, they already have a property CommandTimeout. This property will be used in data preview.

    • For all other components that have data preview feature but don’t have CommandTimeout property:

      • If there is a DWORD value DataPreviewTimeout under system registry key:\\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\SSIS\Preferences SSIS will use that value as time out during preview.

      • If there is no DWORD value DataPreviewTimeout, SSIS still uses 30 seconds.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.