SQL Server 2012集成服务入门与实战指南

需积分: 9 32 浏览量更新于2024-07-27 收藏 23.97MB PDF 举报

《精通 SQL Server 2012 集成服务》是一本由 Francis Rodrigues、Michael Coles 和 David Dye 合著的专业技术书籍，专为 IT 专业人士提供深入学习 SQL Server 2012 集成服务（Integration Services，简称 SSIS）的全面指南。该书旨在帮助读者理解并掌握 SSIS 的核心概念、设计原则以及在实际项目中的应用。本书结构清晰，分为多个章节，方便逐步学习。首先，第1章"Introducing Integration Services"介绍了 SSIS 的基础，概述了其在整个企业数据集成过程中的角色，包括数据迁移、转换和加载等方面。通过这一章，读者可以对 SSIS 的整体架构和工作原理有初步了解。第2章"BIDS and SSMS 11"着重讲解 Business Intelligence Development Studio (BIDS) 和 SQL Server Management Studio (SSMS) 的使用，这是开发和管理 SSIS 包的主要工具，帮助用户创建和配置数据流。接下来的章节逐步深入：第3章的"HelloWorld—Your First SSIS 2012 Package"通过实例演示如何创建第一个 SSIS 包，让读者体验实际操作。之后的章节分别讲解了连接管理器（Chapter 4）、控制流基础（Chapter 5）、高级控制流任务（Chapter 6）、源和目的地适配器（Chapter 7）、数据流变换（Chapter 8）、变量、参数和表达式（Chapter 9）、脚本编程（Chapter 10）、事件与错误处理（Chapter 11），这些都是构建 SSIS 项目的基石。数据质量是至关重要的，第12章"Data Profiling and Scrubbing"介绍了数据清洗和验证的方法，确保数据的准确性和一致性。第13章"Logging and Auditing"涵盖了日志记录和审计功能，这对于监控和维护解决方案的可靠性至关重要。处理不同来源和目标的数据是 SSIS 的一项挑战，第14章"Heterogeneous Sources and Destinations"探讨了如何处理异构数据源和目标。性能优化是另一个关键领域，第15章"Data Flow Tuning and Optimization"提供了优化数据流性能的策略和技巧。对于复杂的解决方案设计，第16章"Parent-Child Design Pattern"讨论了分层设计模式，使得项目更加模块化和可维护。处理维度数据的专门处理方式在第17章"Dimensional Data ETL"中详述。最后一章"Building Robust Solutions"强调了如何构建健壮的解决方案，以应对各种业务需求和变化。在实际部署时，第19章"Deployment Model"会指导读者理解和应用不同的部署策略。全书最后以详尽的索引（Chapter 19）作为总结，便于查找特定主题。《精通 SQL Server 2012 集成服务》是一本实用且深入的参考书籍，无论是初学者还是经验丰富的开发者，都能从中获取到丰富的知识和实践经验，提升在 SQL Server 数据集成领域的技能。

CHAPTER 2  BIDS AND SSMS

Figure 2-2.Folder structure for an Analysis Services project

These folders organize the files in a developer-friendly format. This format also helps with building

and deploying projects. A partial list of the folders is listed below:

 Cubescontains all the cubes that are part of the Analysis Services database. The

dimensions can be created using a wizard, utilizing the metadata stored within the

DSVs.

 Mining Structures apply data-mining algorithms on the data from the DSVs. They

can help create a level of predictive analysis depending on the quality of the data

and the usage of the appropriate algorithm. You can use the Mining Model Wizard

to help create these as well as the Mining Model Designer to edit them.

 Rolescontains all the database roles for the Analysis Services database. These roles

can vary from administrative permissions to restrictions based on the dimensional

data.

 Assemblies holds all the references to Component Object Model,COM, libraries

and Microsoft .NET assemblies.

 TIP: SSIS packages can be used to process cubes. These packages can be executed at the end of a successful

ETL process.

The Analysis Services database, like Integration Services, can source data from a variety of locations

and physical storage formats. The DSVs use the same drivers and are not necessarily limited to the SQL

Server database engine. Prior versions of SQL Server as well as other relational database management

systems (RDBMSs) are available as sources. The languages for querying SQL Server cubes arecalled

Multidimensional Expressions (MDX) and Data Mining Extensions (DMX). Some of the objects that can

be defined in a cube for analytic purposes are measures, measure groups, attributes, dimensions, and

hierarchies. These objects are critical in organizing and defining the metrics and the descriptions of

those metrics that the end user is most interested in.Another important feature that Analysis Services

provides is the concept ofkey performance indicators (KPIs).KPIscontain calculations related to a

measure group. These calculations are vital in evaluating the performance of a business.

CHAPTER 2  BIDS AND SSMS

Integration Services Project

The Integration Services project template enables the developer to create SSIS packages. The package is

the executable work unit within SSIS. It consists of smaller components that can be executed

individually during development, but Integration Services executes at the package level. The debugging

option in Visual Studio will execute the package as a whole,but the control flow executables can also be

individually tested. Figure 2-3 shows a sample Integration Services project. This project will

automatically be added to your current solution if you have one open. Otherwise, a temporary solution

will be created.

 NOTE: Even though Visual Studio has the ability to execute packages, we recommend using the command line

to execute them when testing. Visual Studio debugging mode should be used during development. Wediscuss

more options on running SSIS packages in Chapter 20.

Figure 2-3.Folder structure for an Integration Services project

The following list describes the objects and folders that will appear in your project:

 SSIS Packages contains all the packages associated with the project. These work

units are the actual components that will execute the ETL.All the packages are

added to the .dtproj file. This XML-based file lists all the packages and

configurations that are part of the project.

 Miscellaneous contains all the file types other than the .dtsx files. This folder is

essential for storing configuration files and will be useful for consolidating

connections, especially when utilizing a source control application. Team

Foundation Server is introduced later in this chapter.

 NOTE: With SQL Server 12, data sources and data source views cannot be added to a project. Instead, these

connections can be added to the individual packages. In prior versions, data sources could be added as connection

managers and were allowed to refer to the included DSVs as the tables in source components. Using this

methodology to access data on SQL Server was not the optimal way to extract data.

The debugging option in Visual Studio executes the current package. This feature is useful for watching the

behavior of the package (how quickly rows pass through the components in the Data Flow tasks, how

quickly executables complete, the successful matches of Lookups and Merge Joins, and so forth). During

CHAPTER 2  BIDS AND SSMS

the execution, three colors indicate the status of the executable and data flow components: yellow—in

progress, red—failure, and green—success. This use of different colors is helpful during development

because it shows how the precedence constraints and the Data Flow tasks move the data through.

 NOTE: Unless a package calls another package as an executable, the current package is the only one that will

run in Debug mode, not all the packages in the project. Visual Studio will open every package that is called in a

parent-child configuration and execute them. If there are too many packages, certain ones may open with a

Memory Overflow error displayed, but the package may execute in the background and continue onto the

subsequent packages.

TO UNDO OR REDO, THAT IS THE QUESTION

From its introduction in SQL Server 2005, SSIS did not have native undo or redo ability. The buttons for

these operations existed on the toolbar but were permanently grayed out regardless of the changes made

in the SSIS Designer. The most common method to undo changes was to close the package without saving

those changes. Even with source control, this was always a tricky operation to perform. With SQL Server

2012, SSIS developers now enjoy undo functionality that other Visual Studio developers have enjoyed for

many iterations of the software. With SSIS, clicking the OK button commits the changes. To leave the code

unchanged, you should make it a habit to click the Cancel or Close button if you are only reviewing the

code. The undo and redo ability extends to a certain level to the editing of components.

Report Server Project Wizard

The Report Server Project Wizard allows you to automatically create a Report Server project. You have to

specify the connections to data sources, including security information if necessary, queries for the

reports, and so forth. After the project has been created, the Report Designer can be used to make all the

modifications.

Report Server Project

The Report Server projectallows the developer to create all the objects necessary for reports. The reports

can be deployed on a portal, where end users can access them. The portal can utilize SharePoint, where

users can even save their own reports that they frequently utilize. Usually a web browser is used to

access the reports. Reports should use a cube if they summarize data (that is, perform counts, sums, and

averages). If the reports display detail-level information, querying a database is most likely the more

efficient route. Including too much detail on a report can impact the load time on the end user’s browser

as well as the query time on the server. Figure 2-4 demonstrates the folder structure that is available for

Report Server projects.

CHAPTER 2  BIDS AND SSMS

Figure 2-4.Folder structure for a Report Server project

The folders contain objects that perform the following tasks:

 Shared Data Sourcescontains necessary components of the Report Server

project. These components allow the reports to connect to the data sources that

will be the basis for the reports.

 Shared Datasetscontains multiple reports used to source common datasets.

 Reports stores all the .rdl files that are the actual reports. You can modify the

reports by using the designer.

When you are creating reports, the Design view enables you to modify the visual layout of the page

and the code for the data that goes into each element. Depending on the source for the data, you will

have to use the appropriate variant of SQL or MDX. Sources for reports can include RDBMSs, report

models, and XML. In addition to the Design view, there is a Preview view that can be used to run the

report to make sure that the data renders as desired. Using this view caches the data locally on the

developer’s machine, so clearing the cache often is recommended.

Import Analysis Services Database

The Import Analysis Services Database project template automatically generates all the items of an

Analysis Services project. The wizard asks you to point to anAnalysis Services database. The wizard will

reverse-engineer all the project items based on the existing database. The project can be used to modify

the objects and redeploy them to the server to update the database.

Integration Services Project Wizard

The Integration Services Project Wizard will automatically generate all the items of an Integration

Services project. The wizard will ask for the source of an existing project (either the .dtproj file or a

package deployed on a SQL Server instance). This wizard will import all the objects from the existing

project.

Report Model Project

The Report Model project utilizes SQL Server databases to generate reports. By definition, a report model

stores the metadata of its sources and their relationships. The data sources allow you to access the DDL

of the specified source and utilize it for the report models. The DSVs allow you to store the metadata

from the sources and generate models for reporting. The Model Designer can create report models by

using the SQL Server or Oracle 9.2.0.3 and later versions of the RDBMSs. While models based on an

RDBMS can be modified, those based on Analysis Services cannot. All the data within a data source is

automatically included in the model. Figure 2-5 demonstrates all the objects and folders available to a

Report Model project.

剩余627页未读，继续阅读

天使之梦魇

粉丝: 26
资源: 102

SQL Server 2012集成服务入门与实战指南

S.Q.LSQL全称

Classical Book: Feedback Control Theory-J.C.Doyle,B.Francis,A.Tannenbaum

ADO.NET技术在SQL Server数据库中的应用.pdf

Francis-xsc.github.io:博客

francis-gurr.github.io：个人网站

FRANCIS

Francis

Bolasso-feature-selection-prediction.zip_0h_Bolasso_francis r. b

Taylor--Francis数据库ppt-西南科技大学图书.pptx

Introduction to Plasma Physics and Controlled Fusion Plasma Physics - Francis F. Chen

最新资源