Monday, March 19, 2012

DataReader ODBC Query Timeout

Hello !

I get in my SSIS Package a Query Timeout in the Datareader!

I Use the ADO.Net OBC Connection with the Connection String:

Dsn=xxx;uid=xxx;connection timeout=0;command timeout=0;query timeout=0

Is there any Option to set the Query Timeout ?

Thanks !

Pseudo

Set your time out values to something other than 0, like 999.

Some ODBC drivers interpet 0 as "use default". Since you didn't say what ODBC connection you are using, I cannot tell if that is your problem or not.|||

I have had this problem with queries on very large datasets coming from an AS 400 where indexes weren't present (that I could find out about anyway) when I was using multiple where clause conditions that resulted in an extremely long running query. What I found was that using a simpler query that initially imported way more data than I actually needed to import was more efficient (from the simplistic viewpoint of how long my processing step took to execute) than did changing the timeout to a value large enough to accomodate the query.

Changing the timeout is a good idea but realize that with data sources that you have little to no control over or lack adequate access to info about, it can be more efficient to simply import a lot of data into a holding table in SQL Server that you subsequently prune in an Execute SQL task before you convert it, etc. At lleast once you get it into SQL Server, you can control it however necessary.

Hope this helps.

|||

Thanks for the answer !!

I have not seen that the Datareader in the SSIS Designer have a Command Timeout property ?
I set this property to 0 and now it works...

But exist a difference between the Datareader Command Timeout property and the ODBC Connection Command Timeout property ?