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.
End of Article
