Bagikan melalui

"Query timeout expired" when trying to Preview the data from the OLE DB Source Editor in BIDS

  • Artikel

If you have an OLE DB Source within a Data Flow in your SSIS package,

image

and you click on the Preview button to get a sample (max of 200 rows) of the data returned by the statement you have configured,

image

and the server takes more than 30 seconds to return any rows, then you get the following timeout message.

image

 

By now, there is no way to tweak that query timeout value, neither the max number of rows sampled, since they are both hardcoded as can be seen here:

Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview (implemented in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.DataTransformationServices.Design.DLL)

public static void ShowDataPreview(string sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection90 externalColumns)
{
IDTSSQLTaskConnection connection = ConnectionManagerToSqlTaskConnection(connectionManager);
if (connection != null)
{
(connection as IDTSSQLTaskConnectionOleDb).SetMaxRows(200);
connection.PrepareSQLStatement(sqlStatement, false);
connection.ExecuteStatement(1, false, 30);

.
.
.

    }
}

Until my next post.

Comments

  • Anonymous
    December 17, 2007
    officially not nice ;)  ... seriously, i'm having this problem too and your undeniable expertise is required whenever you get a chance.  Thanks!!!

  • Anonymous
    December 17, 2007
    Hi Paul, This timeout would only affect you if the SqlStatement you're using for your OLEDB Source doesn't return any row before 30 seconds have elapsed. Is that the case? I mean, if you take the query you're using from your OLEDB Source, and try to run it from SSMS (sending results to text, not to grid), how much time elapses before you see the first row returned from the server? If it takes over 30 seconds to return the first row, it's normally all about optimizing your query on the server side (i.e. providing the most appropriate indexes to be able to retrieve the same results in the most efficient way, utilizing as least server resources as possible). If you need further help, just post your scenario here, and either me or some of my readers will be happy to help you. Thanks, Nacho

  • Anonymous
    December 17, 2007
    Hey Nacho, thank you for your assistance.  i don't want to clutter your comment area with what is a rather large query, but i have made a post to a group that has the full contents of the query there.  the link is:  http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/b12fa406570e1ff9/bcc9fbdd62aeeee5?lnk=st&q=#bcc9fbdd62aeeee5 allow me to qualify the intent of the query.  i am dealing with a db that has a "from_function" and "to_function" column representing stations (like desk stations).  i am quantifying the amount of inventory that moves from one station to the next by matching the "to_function" with the next logical "from_function".  it is sort of recursive in nature (although not necessarily a recursive query).  don't hesitate to ask questions.  fyi it is commented but the purpose of the while loop is to automate iteration of a week to date result set.  thank you for all your help. paul

  • Anonymous
    December 18, 2007
    The comment has been removed

  • Anonymous
    August 16, 2010
    good idea.thanks.