SQL Server 2012 集成服务入门指南

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

"本书《精通 SQL Server 2012 集成服务》由 Francis Rodrigues、Michael Coles 和 David Dye 合著，详细介绍了 SQL Server 2012 的集成服务（SSIS）。内容包括从基础到高级的各种主题，旨在帮助读者深入理解和掌握 SSIS 的使用。" SQL Server 2012 集成服务（SSIS）是 Microsoft 提供的一种强大的数据集成和工作流处理工具，广泛用于企业级的数据提取、转换和加载（ETL）过程。这本书深入浅出地介绍了 SSIS 的各个方面，适合初学者和有经验的数据库管理员或开发人员学习。书中的章节分布如下： 1. **介绍集成服务**：第一章介绍了 SSIS 的基本概念，包括它的功能、用途以及在数据处理流程中的角色。 2. **BIDS 和 SSMS**：第二章讲解了 Business Intelligence Development Studio (BIDS) 和 SQL Server Management Studio (SSMS)，这两个工具是设计和管理 SSIS 包的主要环境。 3. **构建你的第一个 SSIS 2012 包**：第三章通过一个简单的实例，引导读者创建并运行他们的第一个 SSIS 包，理解 SSIS 的基础架构。 4. **连接管理器**：第四章讨论了连接管理器，这是 SSIS 包与各种数据源交互的关键组件。 5. **控制流基础**：第五章介绍了如何在 SSIS 包中设置和管理任务的执行顺序，以及如何使用控制流元素来定义工作流程。 6. **高级控制流任务**：第六章涵盖了更复杂的控制流操作，如条件分支、循环等。 7. **源和目标适配器**：第七章讲解了 SSIS 如何与各种数据源和目标进行交互，包括如何配置和优化这些适配器。 8. **数据流转换**：第八章深入讨论了数据清洗和转换过程，包括各种内置转换组件的使用。 9. **变量、参数和表达式**：第九章介绍了 SSIS 中的动态数据处理，包括变量、参数和表达式的使用。 10. **脚本任务和组件**：第十章涵盖了如何使用脚本任务和脚本组件来扩展 SSIS 的功能。 11. **事件和错误处理**：第十一章讨论了 SSIS 的错误处理机制，以及如何监控和响应包执行中的事件。 12. **数据概况分析和清洗**：第十二章探讨了数据质量检查和清洗的方法。 13. **日志记录和审计**：第十三章讲解了如何实现 SSIS 包的跟踪和审计，以满足合规性和性能监控的需求。 14. **异构数据源和目标**：第十四章介绍了与非 SQL Server 数据源的集成。 15. **数据流调优和优化**：第十五章提供了关于提高 SSIS 包性能的策略和技术。 16. **父子设计模式**：第十六章讨论了如何使用父包和子包来构建复杂的工作流。 17. **维度数据 ETL**：第十七章专门针对数据仓库环境中的 ETL 过程，特别是维度数据的处理。 18. **构建健壮解决方案**：第十八章涵盖了最佳实践，以确保 SSIS 解决方案的稳定性和可维护性。 19. **部署模型**：第十九章介绍了 SSIS 包的部署方法和策略，包括使用新的部署模型。最后，书中还提供了一个全面的索引，方便读者查找特定主题。通过这本书的学习，读者将能够熟练地设计、开发、测试和部署 SSIS 解决方案，有效地管理和处理企业的数据需求。

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页未读，继续阅读

直面人生

粉丝: 22
资源: 8

SQL Server 2012 集成服务入门指南

Microsoft SQL Server 2012 Integration Services

SQL Server 2005 SSIS 整合服務

SQL_Server_SSIS_2008_集成服务

如何利用SharePoint Designer 2007将SharePoint网站与SQL Server数据库进行数据集成，并实现基本的数据操作？

如何使用SharePoint Designer 2007实现与SQL Server数据库的数据集成和操作？

如何在Windows操作系统上安装并配置SQLServer 2008数据库系统？请详细描述安装步骤和配置要点。

假设你是一个SQL server数据库开发人员

在Windows系统上安装SQLServer 2008时，应遵循哪些最佳实践来确保系统的稳定性和性能？

在SharePoint Designer 2007中如何创建数据视图，以展示和操作SQL Server数据库中的数据？

在SpringBoot项目中集成MyBatis并配置数据源连接MySQL数据库时，需要注意哪些步骤和配置？请结合示例文件详细说明。

最新资源