Exporting Data, the Right tool for the Right job

I have just been following a thread about using reporting services 2005 to produce a 55,000 row excel spreadsheet. The initiator of the thread was concerned that the report was locking up and was looking for a fix.

I could suggest migrating to SQL Server 2008 as it has much better memory management and pipelining to ensure large reports run as expected. 

A more considered response is that this looks to me like a data extraction task which would be better suited to designing in integration services. This package could then be scheduled overnight and take load off the reporting server during the working day to make it more responsive to other users.

Even that answer is not really the right one, as you have to wonder what this user is doing with that data and how it will be protected once it has been saved locally.  You also have to question how long the data will be relied on as it could become obsolete very quickly (especially in the uncertain commercial world we live in now).

So my considered answer is to buy the end user a cup of coffee and see what he really needs, and then work on that.

Technorati Tags: Business Intelligence,BI.,reporting services,analysis services,SQL Server