SQLServerIntegrationServices开发指南

### SQL Server Integration Services开发指南 #### 1. 数据源查询与映射文件 每个源或目标都有一个映射文件，其中可以找到数据类型映射的详细信息。如果需要从源表读取数据时提供自定义查询，可以选择编写查询来指定要传输的数据，也可以从文件中打开查询。 #### 2. 开始使用SQL Server Data Tools (SSDT) SSDT是开发和维护SSIS项目时常用的工具，基于Visual Studio 2010，相比之前版本有显著改进，无论是高级用户还是初学者都能更轻松地使用。之前的SSIS版本使用Business Intelligence Development Studio (BIDS) 作为开发环境。 ##### 2.1 打开SSDT 可以通过Microsoft SQL Server 2012下的快捷方式打开SQL Server Data Tools (SSDT)，或者在Microsoft Visual Studio 2010开始菜单文件夹中打开Microsoft Visual Studio 2010。打开后，默认会显示一个包含有用信息的起始页。 ##### 2.2 创建新的SSIS项目 从起始页窗口创建新的SSIS项目的步骤如下： 1. 点击“New Project…”，会弹出一个Windows对话框。 2. 在“Installed Templates”下，展开“Business Intelligence”并点击“Integration Services”，在中间窗格中选择“Integration Services Project”。 3. 将项目命名为“R02_Getting Started with SSDT”，将解决方案命名为“Ch01_Getting Start with SQL Server Integration Services”，路径为“C:\SSIS”，然后点击“OK”，将使用项目部署模型方法（默认）创建一个包含空包的空SSIS项目。 ##### 2.3 添加现有包 在解决方案资源管理器窗格中，右键单击“SSIS Package”文件夹，选择“Add Existing Package”。在“Add Copy of Existing Package”对话框中，将“Package location”设置为“File System”，并选择之前保存的包路径，如“C:\SSIS\Ch01\Ch01R01_ImportExportWizard.dtsx”。 ##### 2.4 打开并编辑包 新包将添加到“SSIS Packages”文件夹下，在解决方案资源管理器中双击包名，在包设计器中打开它。然后可以进行一些操作，如双击“Preparation SQL Task 1”，在“Execute SQL Task Editor”对话框中验证SQL语句属性；双击“Data Flow Task 1”，重定向到“Data Flow”选项卡，依次双击“Source-Department”和“Destination-Department”组件，分别验证表名和连接及表名。 #### 3. SSDT中的主要窗口 |窗口名称|说明| | ---- | ---- | |Package design area|控制流（Control Flow）是最重要的选项卡，开发者在此向SSIS说明包将执行的操作。其他选项卡如数据流（Data Flow）、参数（Parameters）、事件处理程序（Event Handlers）、包资源管理器和进度条（仅在运行时可用）也很重要。| |Solution Explorer|包含项目及其文件，每个项目由项目参数、连接管理器、SSIS包和杂项文件夹组成。| |Properties panel|可以读取和编辑设计区域或解决方案资源管理器中每个选定对象的属性。| |SSIS Toolbox|根据包设计区域中选择的选项卡，其中的任务和组件会有所不同。在控制流选项卡中，分为Favorites、Common、Containers和Other Tasks四个区域。| |Connection Managers|是SSIS包组件与源或目标数据提供者之间的连接，有不同类型的连接管理器，如OLE DB连接管理器、平面文件连接管理器等。| |Variables Pane|SSIS包中的每个任务可以通过变量向其他任务发送信息，此窗格包含包变量及其数据类型、作用域和其他属性。| #### 4. 创建第一个SSIS包 在了解了SSDT环境后，可以创建第一个SSIS包。此包将读取Adventure Works Microsoft示例表中的记录数，并将其存储在SSIS包中。 ##### 4.1 准备工作 可以重用之前创建的包，也可以按照以下步骤从头开始： 1. 打开SQL Server Data Tools (SSDT)。 2. 点击“New Project…”，弹出Windows对话框。 3. 点击“Business Intelligence Projects”选项卡，在“Installed Templates”部分选择“Integration Services Project”。 4. 为SSIS项目提供名称和位置，将创建一个空结构和包。 5. 在解决方案资源管理器中，选择默认创建的空包“package.dtsx”，将其重命名为“P01_FirstSSISPackage.dtsx”。 ##### 4.2 具体步骤 1. 在包级别创建一个到Adventure Works Microsoft示例数据库的OLEDB连接。 2. 在“Configure OLE DB Connection Manager Editor”中，选择“New...”创建新连接，如果连接已存在于数据连接列表中，则在此选择它。确保在包设计器区域中选择了“Control Flow”选项卡。 3. 从SSIS工具箱中拖放“Execute SQL Task”到控制流设计界面。 4. 双击编辑“Execute SQL Task”或右键单击并选择“Edit”选项。 5. 将任务的“Connection”属性设置为步骤2中创建的连接。 6. 将“Result set”设置为“Single row”。 7. 添加SQL语句“SELECT COUNT(*) AS NR_ROWS FROM SalesLT.Customer”以获取客户表中的记录数。 8. 从SSIS工具箱中拖放“Script Task”到包设计器区域，并双击编辑它。 9. 创建一个新变量来存储上一个任务提供的值，并在脚本中添加一个消息框，代码如下： ```vb MsgBox (Dts.Variables(0).Value, MsgBoxStyle.Information) ``` 10. 按F5运行包。 #### 5. 熟悉Data Flow任务 控制流选项卡用于操作包的主要工作流，而数据流选项卡用于在源和目标之间转换和移动数据。 ##### 5.1 准备工作 为了熟悉Data Flow任务，以将Excel文件内容合并到数据库（如SQL Server数据库）为例，具体步骤如下： 1. 打开SQL Server Data Tools (SSDT)。 2. 创建一个新项目并提供名称和位置。 3. 打开默认创建的空包“package.dtsx”，将其重命名为“P01_DataFlowTask.dtsx”。 ##### 5.2 具体步骤 1. 在包设计器中选择“Control Flow”选项卡。 2. 从SSIS工具箱中拖放一个“Data Flow”任务到控制流。 3. 双击任务或右键单击并选择“Edit”来打开“Data Flow”进行编辑。 4. 确保在包设计器中选择了“Data Flow”选项卡，此时数据流自然为空。 5. 从SSIS工具箱中拖放“Excel Source”组件（在“Data Sources”组下）到包设计器区域。 6. 双击“Excel Source”组件，点击“New”按钮创建与源Excel文件的连接。 7. 将Excel文件的路径设置为“C:\SSIS\Ch01_Getting Start with SSIS\FILES\R04_NewCustomers.xlsx”，然后点击“OK”。 8. 在“Excel Source Editor”中，将Excel工作表的名称设置为“Sheet1$”。 9. 选择“Columns”选项卡，选择将在数据流中使用的“Firstname”和“Lastname”列。 10. 点击“OK”完成“Excel Source”的编辑。 11. 从SSIS工具箱中拖放“Data Conversion”到包设计器。 12. 选择“FirstName”和“LastName”列，将每列的长度更改为50个字符。 13. 从SSIS工具箱中拖放“Lookup”组件到包设计器。 14. 保持所有属性为默认值，创建与SQL目标数据库（AdventureWorksLT）的连接。 15. 在选择表或视图的列表中，选择目标表“SalesLT.Customer”。 16. 将源转换后的“Copy of FirstName”和“Copy of LastName”列映射到目标SQL列。 17. 点击“OK”完成“Lookup”的编辑。 18. 从SSIS工具箱中拖放“OLE DB Destination”组件到包设计器，以将数据加载到SQL中。 19. 将“Lookup”的“No Match Rows”输出链接到目标，仅插入目标中尚不存在的记录。 20. 编辑目标组件，设置SQL连接和目标表，保持其余控件的所有默认值。 21. 点击“OK”完成“OLE DB Destination”组件的编辑。 22. 按F5运行包。 这个Data Flow从Excel文件中读取一些客户数据（名和姓），应用一些常见的转换并将数据插入到名为“SalesLT.Customer”的SQL表中。 #### 6. SSIS 2012与以前版本的开发者体验差异 SSIS 2012相比以前版本有很多变化，不仅包括SSDT和设计方面的变化，还包括与外部包交互、包部署、新任务和转换等方面的变化。 ##### 6.1 准备工作 最好同时安装SSIS 2012和SSIS 2008，SSIS 2012是必需的，SSIS 2008可选，有助于进行比较。 ##### 6.2 具体差异 1. **开发环境**：SSIS 2012的SSDT是Visual Studio 2010，编辑器有很多改进。早期版本的SSIS使用不同版本的Visual Studio，如SSIS 2008使用Visual Studio 2008，SSIS 2005使用Visual Studio 2005。 2. **SSIS工具箱**：创建新包时，SSIS 2012的SSIS工具箱与SSIS 2008版本不同。之前版本只有三个部分：Control Flow Items、Maintenance Plan Tasks和General。在SSDT 2010中，分为Favorites、Common、Containers和Other Tasks四个区域。Favorites区域可以通过右键单击任务并选择“Move to Favorites”将任务移动到此处。 3. **其他改进**： - SSIS工具箱下有每个任务或容器的快速描述。 - 包设计器中有两个图标用于在工具箱窗格和变量窗格之间切换。 - 控制流选项卡上有滚动条放大镜，可以放大控制流视图，也可以选择自动适应。 - SSDT 2012包设计器中有一个新的“Parameters”选项卡。 - 有一个撤销图标，SSIS 2008和2005缺乏撤销操作，而在SSDT 2012中可以使用Ctrl + Z或点击撤销图标撤销操作。 - 在解决方案资源管理器中可以创建新的项目级连接。 - 右键单击空白控制流区域并选择“Getting Started”，会显示一个新的“Getting Started”窗格。 - 从工具箱中拖放“Script Task”到控制流，在“Script Task Editor”的“Script Language”属性中，可以在Visual C# 2010和Visual Basic 2010之间选择，两者都基于.NET 4.0 Framework。 - 进入数据流选项卡，会看到提示，点击链接可以创建一个新的空数据流。 - SSIS工具箱的“Favorites”部分有“Source Assistant”和“Destination Assistant”，可以通过单个助手组件轻松选择数据源或目标。 这些变化使得SSIS 2012在开发和维护过程中更加便捷和高效。 ### SQL Server Integration Services开发指南 #### 7. 操作流程总结与对比 为了更清晰地展示前面所涉及的各项操作，下面将主要操作流程进行总结，并对比SSIS 2012与之前版本的差异。 ##### 7.1 主要操作流程总结 |操作类型|步骤| | ---- | ---- | |创建SSIS项目|1. 打开SQL Server Data Tools (SSDT)；2. 点击“New Project…”；3. 选择“Business Intelligence Projects”下的“Integration Services Project”；4. 为项目命名并指定位置。| |添加现有包|1. 在解决方案资源管理器中右键单击“SSIS Package”文件夹；2. 选择“Add Existing Package”；3. 设置“Package location”为“File System”并选择包路径。| |创建第一个SSIS包|1. 准备工作：打开SSDT，创建项目，重命名包；2. 创建OLEDB连接；3. 拖放“Execute SQL Task”并设置属性和SQL语句；4. 拖放“Script Task”并添加变量和消息框；5. 按F5运行包。| |使用Data Flow任务|1. 准备工作：打开SSDT，创建项目，重命名包；2. 在控制流中拖放“Data Flow”任务；3. 编辑“Data Flow”，添加“Excel Source”、“Data Conversion”、“Lookup”、“OLE DB Destination”组件并设置属性；4. 按F5运行包。| ##### 7.2 SSIS 2012与之前版本差异对比 |对比项|SSIS 2012|之前版本（以SSIS 2008为例）| | ---- | ---- | ---- | |开发环境|Visual Studio 2010，编辑器有很多改进|Visual Studio 2008| |SSIS工具箱|分为Favorites、Common、Containers和Other Tasks四个区域|只有Control Flow Items、Maintenance Plan Tasks和General三个部分| |撤销操作|有撤销图标，可使用Ctrl + Z撤销|缺乏撤销操作| |新功能|有“Parameters”选项卡、滚动条放大镜、“Source Assistant”和“Destination Assistant”等|无这些功能| #### 8. 操作流程图 下面通过mermaid格式的流程图来展示创建第一个SSIS包的完整流程： ```mermaid graph LR A[打开SQL Server Data Tools (SSDT)] --> B[点击“New Project…”] B --> C[选择“Business Intelligence Projects”下的“Integration Services Project”] C --> D[为项目命名并指定位置] D --> E[重命名默认包为“P01_FirstSSISPackage.dtsx”] E --> F[创建OLEDB连接到Adventure Works示例数据库] F --> G[拖放“Execute SQL Task”到控制流] G --> H[设置“Execute SQL Task”属性和SQL语句] H --> I[拖放“Script Task”到包设计器] I --> J[创建变量并添加消息框] J --> K[按F5运行包] ``` 这个流程图清晰地展示了从打开SSDT到最终运行包的整个过程，有助于更好地理解操作步骤之间的逻辑关系。 #### 9. 常见问题及解决方法 在使用SQL Server Integration Services进行开发过程中，可能会遇到一些常见问题，下面列举并给出解决方法。 ##### 9.1 连接问题 - **问题描述**：在创建OLEDB连接时，提示无法连接到数据库。 - **可能原因**：数据库服务器名称、用户名、密码等信息填写错误；数据库服务未启动；网络连接问题。 - **解决方法**：检查数据库连接信息是否正确；确保数据库服务已启动；检查网络连接是否正常。 ##### 9.2 组件加载问题 - **问题描述**：在添加“Excel Source”等组件时，提示组件无法加载。 - **可能原因**：缺少相应的驱动程序；组件版本不兼容。 - **解决方法**：安装所需的驱动程序；检查组件版本是否与当前环境兼容。 ##### 9.3 运行错误问题 - **问题描述**：按F5运行包时，出现错误提示。 - **可能原因**：SQL语句错误；组件属性设置错误；数据源或目标不可用。 - **解决方法**：检查SQL语句是否正确；仔细检查组件属性设置；确保数据源和目标可用。 #### 10. 总结与展望 通过前面的介绍，我们了解了SQL Server Integration Services的基本操作，包括数据源查询、使用SSDT创建项目和包、使用Data Flow任务以及SSIS 2012与之前版本的差异等内容。这些知识为我们进行数据集成和处理提供了有力的工具和方法。 在今后的开发中，随着技术的不断发展，SQL Server Integration Services可能会有更多的功能和改进。例如，可能会进一步优化性能、增加更多的数据转换和处理功能、提供更便捷的部署和管理方式等。我们可以持续关注相关的技术动态，不断学习和掌握新的知识，以更好地应对实际工作中的各种需求。 同时，在实际项目中，我们可以根据具体的业务需求，灵活运用这些操作和功能，结合其他技术和工具，构建出高效、稳定的数据集成解决方案。例如，可以将Data Flow任务与其他ETL工具结合使用，实现更复杂的数据处理和分析。 总之，SQL Server Integration Services是一个强大的数据集成平台，通过不断学习和实践，我们可以充分发挥其优势，为企业的数据处理和分析提供有力支持。