SQL Server Integration Services (SSIS) and Timeout Errors

Connectivity

SQL Server Integration Services (SSIS) and Timeout Errors

I have a query that takes about 2' 30'' to run in Teradata SQL Assistant and returns 2337 rows. This same query, using the same login credentials returns an error when I try to use it in an SSIS package. The package is designed to move the data from Teradata to SQL Server. Here is the gist of the error:

[Teradata Prod [1]] Error: Teradata.Client.Provider.TdException: [.NET Data Provider for Teradata] [100038] Command did not complete within the time specified (timeout). [Teradata Database] [3110] The transaction was aborted by the user. [Call-Level Interface, Version 2] [245] MTDP: EM_TIMEEXCEEDED(245): Timeout from DBCHWL. at Teradata.Client.Provider.TdCommand.HandleExecuteException(TdException eOuter) at Teradata.Client.Provider.TdCommand.ExecuteRequest(CommandBehavior cmdBehavior, Boolean asynchronousCall) at Teradata.Client.Provider.TdCommand.ExecuteReader(CommandBehavior behavior) at Teradata.Client.Provider.TdCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

I am using the .Net Provider for Teradata:

ADO.NET:Teradata.Client.Provider.TdConnection, Teradata.Client.Provider, Version=1.2.0.0

Other queries, that don't take as long to execute will run successfully using this connection. I have changed the "Connection Pooling Timeout" and the "Connection Timeout" values to both 0 and to 10000 and the package still times out with the same error. I have tried a different login and it still gives me an error.

Where else can I look to try to find a setting? This is a prototype query that will be extended to return many more rows, but if I can't get this simple one to work, I am not sure how I am to proceed with the whole project.

Thanks,

Bob
8 REPLIES
Teradata Employee

Re: SQL Server Integration Services (SSIS) and Timeout Errors

You need to set the TdCommand.CommandTimeout property, which applies to query execution.
The TdConnection.ConnectionTimeout property only applies to the process of making the connection (logging in).

Re: SQL Server Integration Services (SSIS) and Timeout Errors

Thanks! Where does one find this setting? It doesn't appear to be on the property page of the connection object. Or on the property page of the SSIS Data Flow Component (which is the command object).

But there doesn't seem to be any way to modify the TdCommand.CommandTimeout property in SSIS. I imagine it could be done in C# easily enough, but I am not seeing it in SSIS.
Teradata Employee

Re: SQL Server Integration Services (SSIS) and Timeout Errors

Switch to "Data Flow" tab (vs. Control Flow) and select the DataReader source. The Properties window will show CommandTimeout at the very top; it is set to 30 seconds by default.

Re: SQL Server Integration Services (SSIS) and Timeout Errors

Awesome.

Of course "CommandTimeout" wasn't on my version of SSIS until I installed SQL SP2. Apparently the computer I was given wasn't provided with the most up to date software. Even though MS said "CommandTimeout" was fixed in SP1 I had to install SP2 to get it.

But it's there now.

Thanks!

Bob
Fan

Re: SQL Server Integration Services (SSIS) and Timeout Errors

I have a similar problem but i have error in SSAS and not SSIS , when running on datasource view the query runs fine , it displays results with no trouble but when i try to process the cube it fails.

Any suggestions
Teradata Employee

Re: SQL Server Integration Services (SSIS) and Timeout Errors

What is the error message?
I do not fully understand the scenario; but there are few different timeouts.

In BIDS:
Select Tools -> options
Select Business Intelligence Designers
Select Analysis Services Designers
Connection Timeout
Query Timeout

In BIDS:
Right Click on the Data Source
Select Open
Query Timeout

In SQL Server Management Studio:
Connect to SSAS
Select SSAS in Object Explorer
Right Click and select Properties
ExternalCommandTimeout
Fan

Re: SQL Server Integration Services (SSIS) and Timeout Errors

I get those error messages, It seems to be a similar problem, my SSAS cube works fine most of the time but there are time periods that we encounter such a problem with no consistency behavior either side (Teradata, OLAP):

[.NET Data Provider for Teradata] [100002] Cannot create connection within the time specified.. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'ds_Tera_BHI'

[.NET Data Provider for Teradata] [100038] Command did not complete within the time specified (timeout).
[Teradata Database] [3110] The transaction was aborted by the user.

Is there any rule of thumb to configure timeout variables?

I'm using .NET Data Provider for Teradata v13.01, SSAS2005, Win 2008 R2, SP3 and Current timeout configuration:
Connection timeout = 300, I tried to set it on 48000 with no improvment
Connection pooling timeout = 0
Max Pool size = 2
Node down timeout = 48000
Command timeout = 99999
Query timeout = 10 min

Thanks,
Miron
Fan

Re: SQL Server Integration Services (SSIS) and Timeout Errors

I think we resolved the problem -
It seems that the timeout parameters (connection & command timeout) in server side are overriding the .NET Data Provider timeout parameters that mentioned above...