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

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

  • Comments 5

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.

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • officially not nice ;)  ... seriously, i'm having this problem too and your undeniable expertise is required whenever you get a chance.  Thanks!!!

  • 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

  • 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

  • Paul,

    I'm not an expert in SSIS, but I have been trying to setup something similar to what you described and I cannot find any way to put a SQL Command with SQL Command Text which includes references to temp tables without getting an error when it tries to prepare the command batch. So, for example, I used the following command text:

    ============================

    create table #master_transaction

    (

           date_picker smalldatetime,

           moved_lp varchar(20),

            moved_to_id varchar(20),

            transacted_start_from_to varchar(100),

            moved_from_to varchar(100),

            value_stream varchar(20)

    );

    select * from #master_transaction;

    drop table #master_transaction;

    ============================

    and I got back the following error when either trying to Preview the data in the source, or when trying to select the Columns or Error Output pages, or when trying to accept (clicking OK).

    ============================

    TITLE: Microsoft Visual Studio

    ------------------------------

    Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.

    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".

    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Invalid object name '#master_transaction'.".

    ------------------------------

    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    ============================

    The reason is that SQL Server cannot prepare a statement which refers to unexisting objects (preparing a plan for a stored procedure is an exception to this rule), and the temporary table referenced in my simple example above doesn't exist yet, so I get the error.

    The only way I've found to do that is by wrapping the whole batch within a stored procedure, but then I've found another limitation. When you go to the Columns page, it asks SQL Server to prepare an execution plan for the stored procedure, and then with the handle to the prepared plan, it asks SQL to execute it but having SET FMTONLY ON. The effect of that session setting is that when SQL Server is running a query execution plan, it doesn't return any data, but only the metadata, which is what the OLE DB Source Editor actually needs at this point, right? It only needs the resulting column names and their datatype details. The problem is that since the temp table doesn't exist when the prepared plan is executed under the effects of FMTONLY ON, it returns no metadata as if the SP wouldn't return any rowset.

    Maybe in your case you're hitting the timeout first, probably because only to compile a plan (for the prepare) and execute it (even though no rows will be returned because FMTONLY is enabled) it's already exceeeding the 30 seconds.

    So, in your case, if you would like your query to return quickly so that you are able to move out of that blocking situation, you could short circuit the Query Optimizer by appending an expression to your SARG that is not possible. You could append something like "AND 1 = 0". That way, the optimizer will simplify the execution plan to return nothing but the metadata, and immediately.

    Please let me know whether this information is sufficient. Otherwise, feel free to keep the thread open and posting here any further questions or concerns.

    This may end up deserving a completely new post. :-)

    Thanks,

    Nacho

  • good idea.thanks.

Page 1 of 1 (5 items)