Query timeouts(30 Sec) when I use SQL Server 2005 Integration Services

Connectivity

Query timeouts(30 Sec) when I use SQL Server 2005 Integration Services

I am using MS SQL Server 2005 Integration Services with Teradata as a Data Source. The task falls over after 30 secs with the following message.

DataReader Source [1]] Error: System.Data.Odbc.OdbcException: ERROR [HYT00] [NCR][ODBC Teradata Driver] Query timeout expired at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.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)

This is a message passed back from the driver. It is interesting to note that I can get a small query to run no probs if it takes under 30 secs to return. I have seet all timeout settings to 0 where i can but to no avail. Any ideas please?
1 REPLY
Teradata Employee

Re: Query timeouts(30 Sec) when I use SQL Server 2005 Integration Services

I think SSIS does not pass the CommandTimeout to the ".NET Data Provider for ODBC".
I suggest opening an incident with Microsoft.

You can verify that the .NET Data Provider for ODBC supports CommandTime using:

static void Main(String[] args)
{
using (OdbcConnection cn = new OdbcConnection(@"DSN=X;UID=Y;PWD=Z"))
{
cn.Open();

OdbcCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT DATE";
cmd.CommandTimeout = 0;

using(OdbcDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
}

}
}


Replace the Data Source (DSN), User ID (UID), Password (PWD) and the CommandText.