Datareader Query Timeout

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds

Any help would be appreciated.

I am having a similar issue to this that I cannot find the answer for¡­

I have a ¡°Foreach Loop Container¡± in SSIS that contains an ¡°SQL Task¡± I am using to load a series of flat files into a table and do some more processing. I am using an ADO connection. The process works correctly until it encounters a file that takes longer then 30 seconds to process. When that happens I get a ¡°Query Timeout Expired¡±. I am not able to reduce the time the process is taking, I really want to extend the timeout.

I have changed my ADO connection string to:

Data Source=.;Initial Catalog=OST_ACH;Provider=SQLNCLI.1;Integrated Security=SSPI;General Timeout=0;Auto Translate=False;command timeout=600;query timeout=600;

To no avail; any file that takes longer then 30 seconds will cause the error.

There is no VB involved, just the Foreach Loop Container¡± in SSIS that contains and ¡°SQL Task¡±. Thoughts