SSIS最全整合教材模板

SQL SERVER 2005 32bit IS 开发与维护开发与维护 1 SSIS 介绍介绍 IS 是生成高性能数据集成解决方案 ETL 的平台 此平台能提供了多项功能 1 生成和调试包的图形工具和向导 2 执行工作流函数 如 FTP 操作 执行 SQL 语句或发送电子邮件的任务 3 提取和加载数据的数据源和目标 4 用于清理 聚合 合并和复制数据的转换 5 管理 IS 的管理服务 6 对 IS 对象模型编程的应用程序编程接口 API 体系结构 IS 运行时保存包布局 运行包 并为日志记录 断点 配置 连接和事务提供支持 2 新建项目新建项目 点选菜单 文件 新建项目 在新建项目属性框中选择 Integration Services 自定义项目名称 项目位置 其他设置保持默认 然后按 确定 初始开发界面如下 新建项目过程中 系统自动生成以下文件目录结构 C SSSIS Project ALM Project ALM Project sln ALM Project ALM Project dtprojt Package1 dtsx ALM Project database sln 解决方案文件 一个或多个项目文件的集合 dtprojt 项目文件 包含了项目的配置 数据源和包的信息 database 包含 BIDS 打开 Integration Services 项目所需要的信息 解决方案管理器提供了整个项目的对象浏览 数据源 包含可以由多个包引用的项目级数据源 数据源视图 它们基于数据源并可以由源 转换和目的地引用 SSIS 包 存放 SSIS 包 杂项 杂物袋 存放除了以上文件之外的文件 3 配置项目配置项目 2 1 数据源 数据源视图配置 点击 数据源 新建数据源 然后按向导逐步配置 点击 数据源视图 新建数据源视图 然后按向导逐步配置 4 SSIS4 SSIS 包设计包设计 包设计既可通过导入 导出向导完成基本包的构建 也可通过 SSIS 设计器完成 包 设计主要涉及控制流 数据流 事件处理 日志处理的构建 3 1 构建控制流 3 1 1SSIS 提供大量的控制流任务 构建控制流时需将相应的控制流控件拖入控 制流操作界面 3 1 2 设置各控件的属性 Foreach 循环容器 FTP 任务 3 2 1 构建数据流 最典型的数据流具有一个源 带有一个输入和一个输出的转换以及一个目标 3 2 1SSIS 提供大量的数据流任务 构建数据流时需将相应的数据流控件拖入数 据流操作界面 3 2 2 设置各控件的属性 Excel 源 数据转换 数据目标 5 5 包的调试包的调试 5 1 1 SSIS 可以让你设置两种不同的断点 一个在包 任务和存储器上 另一种就 是在脚本对象里 在设断点时 我们要记住这些断点只能在控制流上的项目上进行设置 而不能在数据流任务上进行设置 要在包上设置断点 你就要进入控制流页面 在包的面上单击右键 选择 编辑断点 如果你想为任务或容器设置断点 就可以在你感兴趣的任务或容器上单击右键 再选择 编辑断点 编辑断点 会话如下 运行包的时候 在每次循环迭代开始时断开 事件就停止 你就可以访问所有的本 地变量 用户自定义变量以及暂停之后表示包的状况的视图 5 1 2 SSIS 提供数据查看器解决数据流内部的故障问题 设置好后 执行包 我们可以查找有害数据 保证它们能够正常运行 为了让这些包再次运行 我可以 点击 Play 按钮或点击分离 如果我选择 Play SSIS 包就会继续运行 下次运行同样 的任务时 例如在进行 loop 操作时 我可以给目前的数据获取另一个数据阅读器 6 6 包的执行包的执行 MS 提供多种执行包的方法 1 服务类型为 IS 的 SSMS 即 dtexecui 工具 2 dtexec 命令行 3 SSDS 执行包程序 6 1 在开始 运行 输入 dtexecui exe 6 2 dtexec 命令行 参见 SQL Server 2005 联机丛书 dtexec 实用工具 6 3 SSDS 执行包程序 方法一 解决方案 SSIS 包 右击 执行包 方法二 Ctrl F5 7 7 事件处理事件处理 事件处理程序可以执行诸如下列任务 当包或任务运行完成时清除临时数据存储 在包运行前检索系统信息 以便评估资源可用性 在引用表中的查找失败时刷新表中的数据 当发生错误或警告时 或者当任务失败时 发送电子邮件 如果事件没有事件处理程序 则将该事件提升到包中容器层次结构中的上一级容器 在事件管理器页面选择事件级别 及事件类型 设置完级别后 后续处理参见 3 包设计 8 8 日志记录管理日志记录管理 SSIS 包含可用来在包 容器和任务中执行日志记录的日志提供程序 通过日志记录可 以捕获有关包的运行时信息 从而帮助您在每次运行包时对其进行审核和故障排除 文本文件日志 将日志项以逗号分隔值格式写到文本文件 默认文件扩展名是 log SQL Server Profiler 日志 写入可用 SQL Server 事件探查器查看的 trc 跟踪 SQL Server 日志 将日志项写入 SQL Server 2005 数据库中的 sysdtslog90 表 Windows 事件日志 将日志项写入本地计算机上 Windows 事件日志中的应用程序日志 XML 文件日志 将日志文件写入 XML 文件 从菜单选 SSIS 日志记录 执行完包后 查看日志 9 9 配置及部署包配置及部署包 可对开发完成的包进行配置 通过设置包级属性来实现安全性 从检查点重新启动包 或将事务合并到包工作流中等功能 使用配置 不必对包进行修改即可更新属性 包运行 时 将加载配置信息并更新属性值 选择 SSIS 包配置 进入配置 此配置可以应用与多个包 配置完成后 通过创建部署实用工具 然后生成 IS 项目 在生成项目时 将自动包 括项目中的所有包和包配置 若要部署其他文件 如项目的自述文件 请将这些文件放在 IS 项目的 杂项 文件夹中 选择项目或者解决方案 右击 属性 部署实用工具 注意 CreateDeploymentUtility 必须被设置为 True 设置完部署工具后 就可以生成部署文件了 生成的部署文件如下 完成部署文件生成后 需要将部署文件部署到文件系统或者 SQL SERVER 数据库 双击 SSISDeploymentManifest 调用 包安装向导 10 SS10 SS ManagmentManagment StudioStudio 管理 监视包管理 监视包 使用 SSMS 通过 Windows 提供的 IS 服务实现对包的管理及监控 在管理 IS 包之前 需现配置 IS 服务及启动 IS 服务 此服务的配置文件为 Program Files Microsoft SQL Server 90 DTS Binn MsDtsSrvr ini xml 该默认配置文件指定以下内容 服务停止时将向包发送停止命令 在 SSMS 的资源管理器中为 IS 显示的根文件夹是 MSDB 和 文件系统 文件夹 IS 服务所管理的文件系统中的包位于 Program Files Microsoft SQL Server 90 DTS Packages 中 true MSDB File System Packages 如修改 MSDB 其格式为 服务名 实例名 如修改文件系统 直接设置文件目录 启动服务后 运行界面如下 如需性能监控 可通过 控制面板 管理功能 性能 添加计数器日志 添加 SSIS Pipeline 性能对象 选择所有计数器 详细的日志数据可以在 SSIS Pipeline 计数器属性对应的目录内查看 运行前 运行后 11 包的高级功能介绍 后续再讲包的高级功能介绍 后续再讲 11 1 包继承 11 2 启动点 SQLSQL SERVERSERVER SSISSSIS 实例实例 数据的导出涉及到数据转换的概念 企业中的数据并不是以统一的格 式存储的 比如 有 SQL access oracle excel 这些存储形式 的格式都是不一样的 但是我们可以利用 SSIS 包设计器来对这些不 同格式的文件之间进行转换 以便更加灵活地使用企业的数据 为什么要进行数据转换 为什么要进行数据转换 当我们希望将数据从一个服务器移动到另一个服务器或者另外一个地 方 或者需要对数据进行复制 存档 迁移的操作 需要数据转换 数据转换前该考虑什么问题 数据转换前该考虑什么问题 更改数据的格式 数据的重构和映射 数据的一致性 验证数据的有 效性 如果数据是一致的 但是格式不一致 或者数据的表现形式不 统一 那么是不行的 SQL 的数据转换工具有哪些 的数据转换工具有哪些 SQL 的数据转换的方式是多样的 但是他们的功能各有侧重 所以需 要我们根据实际的需要有选择的使用 1 使用使用 T SQL 语句 从现有表中选择数据并添加到表中 语句 从现有表中选择数据并添加到表中 2 备份和还原 备份和还原 3 分离和附加 分离和附加 4 复制 该功能在前一篇文章有介绍 复制 该功能在前一篇文章有介绍 5 SSIS 导入和导出向导 导入和导出向导 6 SSIS 包设计器 包设计器 我们使用 SSIS 包设计器可以定义更加复杂的数据流 做比 SSIS 向 导更加精确和专业的转换 项目需求 在公司的数据库中 有一个表叫项目需求 在公司的数据库中 有一个表叫 玩家一览表玩家一览表 总经理希 总经理希 望查看该表的内容 但是总经理不会使用望查看该表的内容 但是总经理不会使用 SQL 的查看方式 只会使用的查看方式 只会使用 excel 并且总经理只希望看到 并且总经理只希望看到 姓名姓名 年龄年龄 种族种族 职业职业 字字 段 段 项目分析 根据以上的需求 我们需要使用项目分析 根据以上的需求 我们需要使用 SSIS 包设计器将结果导包设计器将结果导 出到出到 excel 表格中 方便总经理的查看 表格中 方便总经理的查看 先说说该实现该项目的整体思路 先说说该实现该项目的整体思路 1 启动启动 BIDS 2 新建项目新建项目 3 新建数据源并连接数据源新建数据源并连接数据源 4 添加数据流任务添加数据流任务 5 添加并编辑数据流源添加并编辑数据流源 6 添加并编辑添加并编辑 派生列派生列 转换方法转换方法 7 添加并编辑数据流目标添加并编辑数据流目标 8 验证验证 下面来看看具体的实现步骤 一 我们要用到一个工具 BIDS 打开该工具 新建项目 如图 BIDS 的界面如图所示 文件 新建项目 或直接点击新建项目按钮 二 创建完成项目后 新建数据源 并连接该数据源 如图 连接管理器配置后 如图所示 右击连接管理器 从数据源新建连接 配置完成的数据源如图所示 三 添加数据流任务 即我们要设计什么样的任务流 等这个流 定义完成 会一个包的形式保存 且可以重复执行将数据流任务拖动 到右边的窗口 四 添加并编辑数据流的源 数据库源选择 OLE DB 源 即要从 数据库向外导出数据 首先将 OLE DB 源拖动到右边的编辑界面 然后右击该源 编辑 弹出编辑界面 做如下的配置 五 因为总经理需要查看该表中的年龄字段 而该表中没有该字 段 只有出生日期字段 所以我们需要配置 派生列 如下图 将派生列域 OLE DB 源进行连接 单机 OLE DB 源的向下绿色箭头 拖动到派生列 右击派生列 进行相关的编辑 配置完成的表达式 如图所示 五 派生列编辑完成后 我们要添加并编辑数据流的目标 即 ex cel 目标 如图所示 采用同上述类似的方法 先将数据流目标拖动到主界面 然后编辑数据流目标 并且将数据流目标和派生列用向下的绿色箭头 相连 配置好后 如图所示 设计好的 SSIS 包 如图所示 六 最后我们来验证一下 单击工具栏中的调试 执行 看看 数据是不是成功导入到桌面的 excel 表格了 我们打开 excel 表格看看 有我们刚才导入的数据 完好无损 并且 包含年龄字段 该字段是在 派生列 里根据出生日期计算出来的 包部署包部署 创建部署实用工具创建部署实用工具 1 在 SSIS 中 打开要部署的项目 在解决方案资源管理器下右击项目名 在弹出的菜单 中选择属性 打开属性对话框 图 1 图 1 2 在属性页左边配置属性中选择部署实用工具 在右边杂项中把 CreateDeploymenUtility 选项设置为 True 图 2 该属性只有设置为 True 才能创建部署包实用工具 在 DeploymentOutputPath 属性可以设置部署实用工具的位置 默认是在项目文件夹的 bin Deployment 目录下 图 2 3 在解决方案资源管理器中 右键单击该项目 再单击 生成 或是在菜单档中单击生 成菜单下的 生成 选项 图 3 图 3 4 在左下角 输出 窗口中查看生成进度和生成错误和生成情况 图 4 安装包安装包 1 在项目文件夹的 bin Deployment 下找到后缀名为 SSISDeploymentManifest 的文件 双 击打开 进入包安装向导 图 6 选择部署到 SQL SERVER 图 7 也可以部署到文件系 统 部署到文件系统生成的包会放在本地 再用移动存储设备把生成的包拷到目标机 在 服务器名称选择目标面 SQL 服务器名字 如果目标机有密码选择 使用 SQL SERVER 身 份验证 输入用户名和密码 图 8 点击下一步 再点下一步完成包安装 图 10 图 5 图 6 图 7 图 8 图 9 图 10 2 打开目标机 SQL Server Mangement Stdio 连接