All Forums Connectivity
CosmicBob 3 posts Joined 08/08
15 Aug 2008
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.HandleExecuteExcept ion(TdException eOuter) at Teradata.Client.Provider.TdCommand.ExecuteRequest(Comm andBehavior cmdBehavior, Boolean asynchronousCall) at Teradata.Client.Provider.TdCommand.ExecuteReader(Comma ndBehavior behavior) at Teradata.Client.Provider.TdCommand.ExecuteDbDataReader (CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.Ex ecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapt er.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.0Other 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

Fred 1096 posts Joined 08/04
15 Aug 2008

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).

CosmicBob 3 posts Joined 08/08
15 Aug 2008

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.

NetFx 346 posts Joined 09/06
18 Aug 2008

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.

CosmicBob 3 posts Joined 08/08
19 Aug 2008

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

ns100 3 posts Joined 07/08
27 Aug 2008

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

NetFx 346 posts Joined 09/06
28 Aug 2008

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 TimeoutIn BIDS: Right Click on the Data Source Select Open Query TimeoutIn SQL Server Management Studio: Connect to SSAS Select SSAS in Object Explorer Right Click and select Properties ExternalCommandTimeout

mamit 2 posts Joined 08/11
16 Aug 2011

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

mamit 2 posts Joined 08/11
22 Aug 2011

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...

You must sign in to leave a comment.