SSIS 如何创建 ETL 包SSIS How to Create an ETL Package

适用范围:SQL Server,包括 Linux Azure SQL 数据库yesAzure SQL 数据仓库no并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

在本教程中,将学习如何使用 SSISSSIS 设计器创建一个简单的 MicrosoftMicrosoft SQL ServerSQL Server Integration ServicesIntegration Services 包。In this tutorial, you learn how to use SSISSSIS Designer to create a simple MicrosoftMicrosoft SQL ServerSQL Server Integration ServicesIntegration Services package. 所创建的包将从平面文件提取数据,重新设置数据的格式,然后将已重新设置格式的数据插入到事实数据表中。The package that you create takes data from a flat file, reformats the data, and then inserts the reformatted data into a fact table. 在下列课程中,将扩展包以阐释循环、包配置、日志记录和错误流。In following lessons, the package is expanded to demonstrate looping, package configurations, logging, and error flow.

在安装教程所用的示例数据的同时,也会安装将在教程的每一课中创建的完整的包版本。When you install the sample data that the tutorial uses, you also install the completed versions of the packages that you create in each lesson of the tutorial. 使用完整的包,您可以按需要跳过前面几课而从后面的课程开始学习教程。By using the completed packages, you can skip ahead and begin the tutorial at a later lesson if you like. 如果本教程是你第一次使用包或新的开发环境,我们建议从第 1 课开始学习。If this tutorial is your first time working with packages or the new development environment, we recommend that you begin with Lesson1.

什么是 SQL Server Integration Services (SSIS)?What is SQL Server Integration Services (SSIS)?

MicrosoftMicrosoftSQL ServerSQL Server Integration ServicesIntegration Services (SSIS) 是一个可用于生成高性能数据集成解决方案的平台,其中包括数据仓库的提取、转换和加载 (ETL) 包。SQL ServerSQL Server Integration ServicesIntegration Services (SSIS) is a platform for building high-performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. SSIS 包括用于生成和调试包的图形工具和向导;用于执行 FTP 操作等工作流函数、执行 SQL 语句和发送电子邮件的任务;用于提取和加载数据的数据源和目标;用于清理、聚合、合并和复制数据的转换;用于管理包执行和存储的管理数据库 SSISDB;以及用于对 Integration ServicesIntegration Services 对象模型进行编程的应用程序编程接口 (API)。SSIS includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management database, SSISDB, for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration ServicesIntegration Services object model.

学习内容What You Learn

熟悉 MicrosoftMicrosoft SQL ServerSQL Server Integration ServicesIntegration Services 中的新工具、控件和功能的最好方法,就是使用它们。The best way to become acquainted with the new tools, controls, and features available in MicrosoftMicrosoft SQL ServerSQL Server Integration ServicesIntegration Services is to use them. 本教程将指导使用 SSISSSIS 设计器创建一个简单的 ETL 包,其中包含循环、配置、错误流逻辑和日志记录。This tutorial walks you through SSISSSIS Designer to create a simple ETL package that includes looping, configurations, error flow logic, and logging.

必备条件Prerequisites

本教程适用于熟悉基本数据库操作,但对 SQL ServerSQL Server Integration ServicesIntegration Services中的新功能认识有限的用户。This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in SQL ServerSQL Server Integration ServicesIntegration Services.

若要运行本教程,必须安装下列组件:To run this tutorial, you have to have the following components installed:

  • SQL ServerSQL Server Integration ServicesIntegration Servicesand Integration ServicesIntegration Services. 若要安装 SQL Server 和 SSIS,请参阅安装 Integration ServicesTo install SQL Server and SSIS, see Install Integration Services.

  • AdventureWorksDW2012 示例数据库。The AdventureWorksDW2012 sample database. 若要下载 AdventureWorksDW2012 数据库,请从 AdventureWorks sample databases(AdventureWorks 示例数据库)下载 AdventureWorksDW2012.bak,并还原备份 。To download the AdventureWorksDW2012 database, download AdventureWorksDW2012.bak from AdventureWorks sample databases and restore the backup.

  • 示例 数据文件。The sample data files. 示例数据与 SSISSSIS 课程包一起提供。The sample data is included with the SSISSSIS lesson packages. 要将示例数据和课程包下载为 Zip 文件,请参阅 SQL Server Integration Services 教程文件To download the sample data and the lesson packages as a Zip file, see SQL Server Integration Services Tutorial Files.

    • 为了防止意外更改发生,zip 文件中的大部分文件都是只读文件。Most of the files in the Zip file are read-only to prevent unintended changes. 若要将输出写入到文件或更改输出,必须在文件属性中禁用只读属性。To write output to a file or to change it, you may have to turn off the read-only attribute in the file properties.
    • 示例包假定数据文件位于文件夹 C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package 中。The sample packages assume that the data files are located in the folder C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package. 如果将下载内容解压缩到其他位置,必须在示例包中的多个位置更新文件路径。If you unzip the download to another location, you may have to update the file path in multiple places in the sample packages.

本教程中的课程Lessons in This Tutorial

第 1 课:使用 SSIS 创建项目和基本包Lesson 1: Create a Project and Basic Package with SSIS
在本课中,将创建一个简单的 ETL 包,从单个平面文件中提取数据,再使用查找转换转换数据,最后将所得结果加载到目标事实数据表中。In this lesson, you create a simple ETL package that extracts data from a single flat file, transforms the data using lookup transformations and finally loads the result into a fact table destination.

第 2 课:使用 SSIS 添加循环Lesson 2: Adding Looping with SSIS
在本课中,将扩展第 1 课中创建的包,以便利用新增的循环功能,将多个平面文件提取到单个数据流进程中。In this lesson, you expand the package you created in Lesson 1 to take advantage of new looping features to extract multiple flat files into a single data flow process.

第 3 课:使用 SSIS 添加日志记录Lesson 3: Add Logging with SSIS
在本课中,将扩展第 2 课中创建的包,以便利用新增的日志记录功能。In this lesson, you expand the package you created in Lesson 2 to take advantage of new logging features.

第 4 课:使用 SSIS 添加错误流重定向Lesson 4: Add Error Flow Redirection with SSIS
在本课中,将扩展第 3 课中创建的包,以便利用新增的错误输出配置。In this lesson, you expand the package you created in lesson 3 to take advantage of new error output configurations.

第 5 课:添加包部署模型的 SSIS 包配置Lesson 5: Add SSIS Package Configurations for the Package Deployment Model
在本课中,将扩展第 4 课中创建的包,以便利用新增的包配置选项。In this lesson, you expand the package you created in Lesson 4 to take advantage of new package configuration options.

第 6 课:在 SSIS 中对项目部署模型使用参数Lesson 6: Using Parameters with the Project Deployment Model in SSIS
在本课中,将扩展第 5 课中创建的包,以便将新参数用于项目部署模型。In this lesson, you expand the package you created in Lesson 5 to take advantage of using new parameters with the project deployment model.