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

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.