Process SSAS DAtabase: The connection either timed out or was lost

[Database Administrator]

Dear all

I have a sqlserver 2005 sp2 built 3282 on a 64bit box. I receive an error
while processing a SSAS Database out from a SSIS package with a Process SSAS
Database Task:

The connection either timed out or was lost.

This error does happen every second time while processing is done
automatically with SSIS. It never happens while I process a cube out from
Management Studio.

So it looks for me that I have a OLE DB Provider for Analysis Services 9.0
problem - cause the package connect to SSAS with this provider.

or

There could be some settings for a time out for this kind of connections I'm
not aware of.

Can you help me please with this problem?

Thanks in advance,

Marc

I installed the

[Database Administrator]

Hi Marc,
Thank you for using Microsoft MSDN Managed Newsgroup. This is Charles. I will assist you on this issue.

From your description, I understand that you encountered connection time out issue when you processed your
cube in SSIS.
If I have misunderstood, please let me know.

The Analysis Services Processing task used OLE DB provider for MSOLAP to connect to the OLAP data source.
There is a setting named "timeout" for connection string. It sets the time to wait for a command to run before
it stops trying to run the command and generates an error. However by default the value should be 0. I
recommend that you right click the connection in the Connection Manager, click Properties, click the field of
ConnectionString, click Edit..., click All, check if the Timeout value equals to 0. If not, change it to 0 or a larger
value.

If this issue persists, may you let me know how long your SSMS will finish the job and how long the issue
happened in your BIDS from start?


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from
the community or a Microsoft Support Engineer within 2 business day is acceptable. Please note that each
follow up response may take approximately 2 business days as the support professional working with you
may need further investigation to reach the most efficient resolution. The offering is not appropriate for
situations that require urgent, real-time or phone-based interactions. Issues of this nature are best handled
working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services
(CSS) at http://msdn.microsoft.com/en-us/subs.../aa948874.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

[Database Administrator]

Hello Charles

Thanks for supporting me on this issue.

I checked the timeout value in the Editor of my SSAS OLE DB Connection and
it is set to 0. My connectionstring looks like this:

Data Source=localhost;Initial Catalog=SSAS_DB;Provider=MSOLAP.3;Integrated
Security=SSPI;Impersonation Level=Impersonate;

When I process the cube it lasts about 30 minutes for a full process.

In BIDS I did not try but I will do now and post the result.

Kind regards,

Marc

[Database Administrator]

Hello Charles

out from BIDS the processing with a SSIS package takes about 30 minutes and
was successful. I just moved this data warehouse to virtualized 64bit box -
before I did not have this problem. I realized that the last step of
processing takes a long time and nearly no processing power (seen in
taskmanager) is used.

Could this be a reason for the timeout?

Kind regards,

Marc

[Database Administrator]

Hi Marc,
Could you please run "ping -t" to check if the network response is normal during the proces time?
Also, please run Performance Monitor (perfmon.exe) on your server to see if there any any performance
pressure at that time.
The performance monitor trace logs may be large, please first send me (changliw_at_microsoft_dot_com) an
email and then I will create a FTP workspace for your uploading the files.

Thank you!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======