In my last column "Leverage Reporting Services 2005 Independent of Your Database" (http://www.windowsitpro.com/articles/articleid/48629/48629.html), I discussed how Microsoft SQL Server 2005 provides a greatly enhanced version of SQL Server Reporting Services (which I'll refer to as Reporting Services 2005). As part of that discussion, I focused on the new Report Builder and how it can be configured to allow your end users to create the custom reports they want within a schema you define. However, as I noted, many of the more exciting changes in Reporting Services 2005 are associated with integration.
At its core, Report Services 2005's purpose is to let you integrate your SQL Server data with your application in the form of reports. To achieve this end, Reporting Services 2005 has been better integrated with Microsoft Visual Studio 2005. In particular, there's a Report Viewer in Visual Studio 2005 Professional Edition that you can freely redistribute with your Web and smart client applications. This is a very impressive move oriented more toward the smart client market. Because of this integration, you can even embed reports into your application in such a way that you don't need access to a Report Server. So, if your application is running on a laptop that doesn't have Internet access but does have local data or even objects, you can use these items to display a report on the client's machine.
However, the integration goes beyond the developer tools. Reporting Services 2005 integrates with other SQL Server 2005 components, such as SQL Server 2005 Analysis Services. As I noted in my "Get'em While They're Hot" column (http://www.windowsitpro.com/article/articleid/48370/48370.html), Microsoft has significantly enhanced Analysis Services. Reporting Services 2005 now supports accessing the Multidimensional Expression (MDX) queries used by Analysis Services. This extension is important to both Reporting Services 2005 and Analysis Services because it provides end users with a common interface for addressing both transaction and warehouse data.
Reporting Services 2005 is also integrated with Integration Services, another member of what I would call the SQL Server 2005 family of products. (A good diagram showing the relationships between SQL Server 2005's main components is available at http://msdn2.microsoft.com/en-us/library/ms141026(en-US,SQL.90).aspx.) But before I tell you about Integration Services, let me first be upfront by noting that SQL Server 2000 has DTS. DTS lets you transfer data at the most basic level between a SQL Server database and another database, which doesn't need to be a SQL Server database. From a programmatic standpoint, it's then possible to create a package that automates transfers, such as transfers of data between a transaction database and a data warehouse. Realistically, however, the capabilities of DTS are limited, and the packages are difficult to customize and keep running.
With SQL Server 2005, DTS got a makeover and a new name: Integration Services. The makeover includes many changes. For example, with DTS, there's a single area under Enterprise Manager in which you attempt to create, schedule, and run packages. Each package is supposed to not only move data but also control the conditions under which that data should move. More important, there is no good way to debug packages. With Integration Services, these tasks get broken into separate components, the first of which is a dedicated data flow engine.
The data flow engine lets you define one of three basic categories of objects: source, transformation, or destination. You can then build data flows that indicate how data should be translated during a move from the source to the destination. For more information about the data flow engine, check out the Data Flow Elements Web page at http://msdn2.microsoft.com/en-us/library/ms140080.aspx. When you read this Web page, you'll notice that it doesn't talk about how to program a set of tasks around the flow of data-- that process is under the control of the second main component of Integration Services: the runtime and design environment.
The runtime and design environment lets you select one or more of the data flows you have defined, combine that data flow with control logic, and test run that package. This setup separates the definition of the data being moved and the transformation of that data from the conditions surrounding that data's move. In addition, this environment provides you with the ability to debug the package. For more information about the elements that you work with in this runtime environment, read the Control Flow Elements Web page at http://msdn2.microsoft.com/en-us/library/ms137681.aspx.
In my next column, I want to wrap up the year with a discussion of some of the many other features that SQL Server 2005 provides. The good news is that although it took almost 5 years to get here, this updated version of SQL Server has new features in every area, including the core database, T-SQL, Reporting Services, Analysis Services, Integration Services, Replication, Notification Services, and the Service Broker.
