Query timed out in SSIS

Question

Query timed out in SSIS

Ells

SSCrazy Eights

Points: 9901
More actions
January 15, 2009 at 5:15 am

#129469

Thanks for reading and hopefully helping,
I have put the error message at the bottom of this post. ALL SQL is 2005.
I have SSIS on Server2 along with my data warehouse (SQL SERVER). I have my source database on Server1. In my package I use an oledb ource and oledb destination. The source sql statement is 'exec sp_getmydata'. This executes a stored proc on Server2 to fetch and clense data (case statements to deal with nulls and records that need to go from '1' to '01' etc) from Server1.
I am now getting a timeout. Any suggestions on how I can improve the performance?
Many Thanks.
Mark
[OLE DB Source [1]] Error: 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: "Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "**\***". .". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "SQLNCLI" for linked server "PCTCCTSS02\SURREYCMS" reported an error. Execution terminated by the provider because a resource limit was reached.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "*****\***" returned message
"Query timeout expired".".

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Ells (1/15/2009)
Thanks for reading and hopefully helping,
I have put the error message at the bottom of this post. ALL SQL is 2005.
I have SSIS on Server2 along with my data warehouse (SQL SERVER). I have my source database on Server1. In my package I use an oledb ource and oledb destination. The source sql statement is 'exec sp_getmydata'. This executes a stored proc on Server2 to fetch and clense data (case statements to deal with nulls and records that need to go from '1' to '01' etc) from Server1.
I am now getting a timeout. Any suggestions on how I can improve the performance?
Many Thanks.
Mark
[OLE DB Source [1]] Error: 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: "Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "**\***". .". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "SQLNCLI" for linked server "PCTCCTSS02\SURREYCMS" reported an error. Execution terminated by the provider because a resource limit was reached.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "*****\***" returned message
"Query timeout expired".".

Have you tried increasing the timeouts for your linked server connection? If you used the default 0, it doesn't mean it is indefinite. 0 means use the sp_config value.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You should work on tuning the stored procedure. Run it on the source server, check the execution plan, and add indexes or change query as needed.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ells SSCrazy Eights Points: 9901 More actions · Answer 3

Many thanks will look into the timeout. I have a feeling that part of the issue may reside here.

I am struggling to get the tuning of the wuery. My stored proc is as simple as

select

col1,

col2,

col3,

.....,

col N

from remote_server.view_my data

OLEDB source executes the stored proc

OLEDB destination receives the results.

3.5 million rows of data, lots of columns (another performance issue) and about 15 of the columns need some alteration ie a case on the source data, calculating age, calculating time differences.

The whole package takes 1800 seconds to complete on a good day, and today it decided to timeout.

Solution so far

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

1. change OLEDB source to a select statement retrieving all the columns with no column transformations or clensing just straight select.

2. Once the data transfer is complete then tart up the data.

Big question:

This appears to struggle I have multiople processors and 8gig of memory which is used 100% during the operation. So apart from timeout or splitting the query and running parallel how do I improve this?

Many Thanks.

Ells

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Why are you using a linked server? Why don't you just use the actual source as the source in SSIS and skip the overhead of the linked server?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ells SSCrazy Eights Points: 9901 More actions · Answer 5

Jack,

tahnks for the question. There where two possibilities with SSIS on the data warehouse server and the relational source database on another server.

1. the SSIS packages connects to both servers and pumps the data from A to B as it were.

OR

2. the SSIS package connects just to the server it is on and executes a stored proc to get the data.

I chose 2 as I found it easier to maintain the SQL in the stored procs (bit of a lame reason). Untill now there seemed to be no downside. I would as ever like to hear peoples opinion on best practice.

Thanks.

Ells

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

If you are using SSIS, I think going directly to the source will be faster. Using a linked server basically means you have 2 clients the SQL Server and SSIS. I'd either put the query directly in SSIS or write the stored procedure on the source server. In my experience querying across a linked server can be very slow, which may be causing the timeout.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Irish Flyer SSCrazy Points: 2245 More actions · Answer 7

I realize this may be a bit late, but according to Microsoft, the fastest, most efficient way to do what you are doing is with a remote query execution that only returns the result of the query as a collection. This means using the OPENQUERY feature on your select (or procedure execution). Linked servers are inherently faster than SSIS due to the fact that SSIS externalizes the data and then re-internalizes it, while the OPENQUERY feature causes the intermediate result of the remote query to be streamed back as a collection straight into TempDB on the destination server. It does not return the result row-by-agonizing-row (rbar) as a normal cross-platform query does. When the collection begins appearing in TempDB on the destination, any local remaining process can begin, even as it continues to be pipelined across.

My benchmark runs transferring a 33 million row query result were as follows:

Simple cross platform INSERT INTO local-table FROM SELECT * FROM server.database.dbo.table where ...

took 5hr 32min

SSIS package took

took 2hr 46min

OPENQUERY in an INSERT INTO as above

took 2hr 20min

Ells SSCrazy Eights Points: 9901 More actions · Answer 8

Many thanks for that I will certainly look into it. My frustration was that we were only shifting 3.3 million rows and it just ground to a halt.

I believe that most of this was down to the source system.

Cheers.

Mark.