estimated cost of this query exceeds the configured threshold ssis

Question

I have an SSIS package that is failing with the above error.

The database has a query governor limit of 300

I have tried adding an 'Execute SQL' task to my package before the data flow task to execute the statement:

SET QUERY_GOVERNOR_COST_LIMIT 0 

This shows as success during package execution, but my dataflow task is still failing and reporting the limit to be 300.

Source: "Microsoft SQL Server Native Client 10.0 " Hresult: 0x80004005 Description: "The query has been canceled because the estimated cost of this query (336) exceeds the configured threshold of 300. Contact the system administrator.". End Error

How can I override this setting on my data flow task?

Answer 1

Unless you have set the "retain same connection" property on the package, which is not my preference for what it's worth, what you are experiencing is what the documentation states.

Using SET QUERY_GOVERNOR_COST_LIMIT applies to the current connection only 
and lasts the duration of the current connection

Inside your package, a connection is opened for the Execute SQL Task, your query governor statement is issued and that task terminates. A new/different connection is then used in your Data Flow task as part of the OLE DB Source (or ADO.NET Source). That connection has not modified the costing for the governor and therefore it is subject to the QG.

To resolve this, you need to modify your source in the Data Flow. Assuming you just selected the table you wanted, you will need to switch the radio button from Table Source to Query Source (names approximate). As the source for this query, you would then use something like

SET QUERY_GOVERNOR_COST_LIMIT 0;
SELECT
    MT.*
FROM
    dbo.MyTable AS MT;

Edit

Given that it's the target table that is blowing the QG limit, the quick and dirty approach would be to modify your Destination's Connection Manager to set the RetainSameConnection property to True. This will ensure the same connection in your destination transformation has had the cost already modified. There's a screen shot of where you set that in my answer over on this question

SSIS: Default Logging OnError don't work with RetainSameConnection

Other approaches that might work, would be to modify the data load to reduce the query (insert) cost.

• You might be able to get there by reducing your commit size and/or batch size.
• If the target table is heavily indexed, the cost of maintaining all the indexes could be pushing you over the threshold for the query governor so drop and recreate non-clustered indexes before and after the package runs might reduce the insert cost. It may also just kick the can (cost) down the road as recreating the NCIes may make the job take longer
• If you're on enterprise edition and are using partitioning, you might be able to load to an empty partition and swap that in. I assume that is not the case in this situation and as partitioning can make a problem worse if not done well.

Answer 2

There is a way to change it for the whole server, with a GUI option in SSMS.

1. In the Server Properties dialog box, go to the Connections page.
2. Select the option Use Query Governor To Prevent Long-Running Queries.
3. In the box below the option, type a maximum query cost limit. The valid range is 0 through 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum query cost limit.
4. Click OK.

Source: Technet Article: Use the Query Governor to Control Excessive Query Execution