Home > SSIS Best Practices > Frozen Linked Server Connection

Frozen Linked Server Connection

If you have written SQL queries using OPENQUERY with a linked server to Oracle or DB2, you might have already experienced frozen connections, which require reboot of the SQL services. If you have not, consider yourself lucky so far.

Here is what somebody from our architecture team said: “in general SSIS will out-perform linked servers.  Also SSIS packages don’t get frozen connections.”

However, I still get resistance from developers/analysts/managers when I suggest that we convert those troubled stored procedures with OPENQUERY/LINKED SERVER to a SSIS package, to avoid those frozen connections. The misconception is 1) SSIS package is hard to deploy 2) we don’t have time to convert them to SSIS packages 3) code in SSIS packages are not easy to “see”.

I went ahead and converted one the most notorious stored procedures to a SSIS package, to avoid using OPENQUERY/LINKED SERVER to a DB2 source. The effort has paid off so far. It’s been trouble free for 2 straight days now.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: