If you’re currently using BizTalk Server 2004 Standard or Enterprise Edition with SQL Server 2000, then you can use SQL Server 2000 Reporting Services at no additional cost*. For those of you who haven’t yet played with it, SQL Server 2000 Reporting Services is a server-based reporting solution that enables the creation, management, and delivery of business-intelligence reports and is, quite possibly, the best overall add-on to EAI I’ve ever seen!
Using BizTalk Server 2004 and SQL Server 2000 Reporting Service together is extremely simple and extremely powerful as shown in Figure 1 below.
Figure 1: Integration with SQL Reporting Services
In this scenario, I am using the BizTalk Server 2004 SQL Adapter to load flat-file data received from a customer into our data warehouse. Next I use the SQL Server 2000 Report Server to generate a report which compares the customer supplied data to data already in our data warehouse. Lastly, the Report Server emails this report to the customer in an Excel workbook format so the customer can make business decisions based upon the report. All of this was done using “out-of-the-box” components, with no custom code required. In fact, the entire integration took only a few days develop, test, debug and deploy using existing hardware and software. That’s what I call great ROI!
* This assumes you have already purchased your licenses of both BizTalk Server 2004 and SQL Server 2000. Obviously you will need to read and understand the SQL Server 2000 Reporting Services license agreement before proceeding.