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.
Resolution
The issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 3 for SQL Server 2014 /en-us/help/2984923
Cumulative Update 1 for SQL Server 2012 SP2 /en-us/help/2976982
Cumulative Update 10 for SQL Server 2012 SP1 /en-us/help/2954099
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.
