Link to home
Start Free TrialLog in
Avatar of digex

asked on

SSIS freezes when running MDX

We have an issue with a hung SSIS package.  I have narrowed the issue down to a particular data flow task.  Within that data flow task I have data reader source that connects to an Analysis Services instance.  I run an MDX query that returns about 50,000 rows.  It then connects to a data conversion component.  All the data comes in as DT_NTEXT and each column gets converted to Unicode string [DT_WSTR].  It then goes to an OLE DB Destination that is a regular SQL Server table.

The problem is that the task freezes, say 10 to 15 seconds, after it starts.  No errors, no warning, it just freezes.  If you go to activity monitor you see a couple of suspended processes with a wait type of ASYNC_NETWORK_IO and a command of BULK INSERT.  If you kill the SSIS package the suspended processes immediately go away.  This wait type suggests that SQL Server is waiting on the network to complete an operation before it can continue.  Since there is really no network involved here, I was thinking the ASYNC_NETWORK_IO wait type was SQL Server waiting on the SAN disk I/O, but in every other instance involving I/O there is no problem.  Just to test the SAN and disk I/O, in an SSIS package I queried a table (NOT SSAS, just a SQL Server table) and wrote 3 million+ rows to a flat file in about 27 second.

This same problematic SSIS package runs fine from my laptop and on another server which is attached to the same SAN as the machine below, but they do have different fiber cards.

Database engine, SSIS and SSAS are all on the same machine.

Hardware/Software specs:

Windows 2003 64bit Ent. SP2,
SQL Server 2005 64 bit, Ent. SP2
Dual quad core blade
HP Bladesystem w/BL460c
QMH2462 dual port fiber card  driver
MPIO driver 3.00.00
MPIO manager 3.00.00
Storport windows patch KB932755

C:\OS&Programs      40GB
D:\Database      500GB
E:\Logs            250GB
P:\Page file      50GB
Avatar of EugeneZ
Flag of United States of America image

Avatar of digex


Installed SQL2005 update patch 4 to remedy the issue.
Avatar of Computer101
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial