SQL Server 2012 集成服务实战指南

4星 · 超过85%的资源需积分: 9 33 浏览量更新于2024-07-27 收藏 23.97MB PDF 举报

"SQL Server 2012 集成服务操作文档" SQL Server 2012 集成服务（Integration Services，简称 SSIS）是微软提供的一种强大的数据集成和工作流处理工具，主要用于ETL（Extract, Transform, Load）过程，即数据抽取、转换和加载。在SQL Server 2012中，SSIS提供了丰富的功能，能够处理各种复杂的数据集成任务。本书涵盖了SSIS的各个方面，包括以下章节内容： 1. **介绍Integration Services**：本章会阐述SSIS的基本概念，它的功能和用途，以及在企业级数据管理中的重要性。 2. **BIDS和SSMS**：Business Intelligence Development Studio（BIDS）是开发SSIS包的IDE，而SQL Server Management Studio（SSMS）则用于管理和监控SSIS包。这两者如何协同工作会在本章中详细讲解。 3. **创建你的第一个SSIS 2012包**：从基础开始，指导读者如何创建一个简单的SSIS包，理解基本的工作流程和设计界面。 4. **连接管理器**：SSIS支持多种数据源和目标，连接管理器是连接到这些数据源的关键。本章将介绍如何配置和管理不同的连接。 5. **控制流基础**：控制流定义了包的执行顺序，包括任务和容器的使用，以及如何设置条件分支和循环。 6. **高级控制流任务**：进一步探讨更复杂的控制结构，如事件处理和异步操作。 7. **源与目标适配器**：详细介绍如何使用不同类型的源和目标，如数据库、文本文件、Excel等，并演示如何进行数据导入和导出。 8. **数据流转换**：数据流是SSIS的核心，本章将讨论数据清洗、转换规则和数据类型映射等。 9. **变量、参数和表达式**：这些是实现动态行为的关键，用于在运行时改变包的行为。 10. **脚本任务和组件**：对于无法用内置组件完成的任务，可以通过编写自定义的VSTA（Visual Studio Tools for Applications）脚本来扩展SSIS的功能。 11. **事件和错误处理**：学习如何捕获和处理执行过程中的错误，以及如何实现健壮的错误处理机制。 12. **数据概况和清洗**：数据质量检查和清洗是ETL的重要步骤，本章介绍如何使用SSIS进行数据验证和修正。 13. **日志记录和审计**：了解如何设置日志记录以跟踪包执行的详细信息，用于调试和审计目的。 14. **异构源和目标**：处理非SQL Server数据源，如Oracle、DB2或 flat 文件等。 15. **数据流调优和优化**：提高SSIS包的性能，包括并行处理、内存管理和资源分配。 16. **父子设计模式**：使用父包和子包来组织复杂的ETL流程，提升可维护性和复用性。 17. **维度数据ETL**：专注于数据仓库中的维度模型处理，如何从源系统抽取、转换维度数据。 18. **构建健壮的解决方案**：探讨最佳实践，确保SSIS包的稳定性和可靠性。 19. **部署模型**：讲解如何将SSIS包部署到生产环境，以及不同部署策略的考虑因素。最后，附录包含索引，方便读者快速查找特定主题。通过阅读本书，读者将全面了解SQL Server 2012的集成服务，能够熟练设计、开发、测试和维护高效的数据集成解决方案。

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

精忠孤雪

粉丝: 0
资源: 1

SQL Server 2012 集成服务实战指南

SQLServer2012集成服务专家手册英文版

微软SQL Server 2012 集成服务发布组件

SQL Server 2012驱动包

SQL Server 2012 集成服务入门指南

Microsoft SQL Server 2012集成服务深度探索

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

SQL Server 2012链接服务器到PostgreSQL

sql server 2005集成服务指导手册+实例

SQL Server 2012

SQLServer2012

最新资源