实例学习SSIS（一）--制作一个简单的ETL包

最新推荐文章于 2024-06-06 10:21:20 发布

caolaosanahnu

最新推荐文章于 2024-06-06 10:21:20 发布

阅读量7.6k

点赞数 1

分类专栏： SSIS 文章标签： ssis sql server 数据仓库任务数据库工作

本文链接：https://blog.csdn.net/caolaosanahnu/article/details/7895634

版权

SSIS 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

http://www.cnblogs.com/tenghoo/archive/2009/10/archive/2009/10/archive/2009/10/16/ssis_lookup.html

导读：

实例学习SSIS（五）--理论介绍SSIS

参考内容：SQLServer2005的帮助文档。

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/sqltut9/html/d6d5bb1f-4cb1-4605-9cd6-f60b858382c4.htm

ETL包：数据的提取、转换和加载；

通过制作第一个包，可以了解以下内容：

1、配置连接管理器（平面文件和OLE DB）；

2、添加数据流任务；

3、配置平面文件源和OLE DB目标；

4、使用查找转换。

详细制包过程：

准备工作：

（1）文件源：创建一个txt文件：userinfo.txt。

内容如下：1|张三|我是张三|男,2|李四|我是李四|女

（2）创建目标数据表：

下面是我创建的数据库（SSIS）和表（userinfo、usersex）：

userinfo.sql

USE [ SSIS ]
GO
/* ***** 对象:  Table [dbo].[userinfo]    脚本日期: 10/16/2009 10:31:54 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ userinfo ] (
     [ uid ] [ int ] NOT NULL ,
     [ uname ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
     [ udesc ] [ varchar ] ( 500 ) COLLATE Chinese_PRC_CI_AS NULL ,
     [ sid ] [ int ] NULL
) ON [ PRIMARY ]

GO
SET ANSI_PADDING OFF

　　usersex.sql

USE [ SSIS ]
GO
/* ***** 对象:  Table [dbo].[usersex]    脚本日期: 10/16/2009 10:32:21 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ usersex ] (
     [ sid ] [ int ] NOT NULL ,
     [ sex ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]

GO
SET ANSI_PADDING OFF

创建SSIS项目：

在“开始”、“Microsoft SQL Server 2005”，找到 SQL Server Business Intelligence Development Studio，创建Integration Services 项目；

制包过程：

（1）配置连接管理器（平面文件和OLE DB）

平面文件：

在“连接管理器”区域“新建平面文件连接”，起个名子，选好userinfo.txt。

选择行、列分割符，如下图所示：

在“高级”中重命名各个列名，并选择相应数据类型，分别是：DT_I4和三个DT_STR。

OLE DB：

（2）在数据流选项卡中创建平面文件源。

从左侧“数据流源”中拖拽即可，确认一下列：

（3）使用查找转换

从左侧“数据流转换”中拖拽“查找”。

因为userinfo.txt中性别是男/女,所以需要转换成对应的性别id才能插入到数据表中。

如下图配置，指定连接列和选择查找列：

（4）配置OLE DB目标

从左侧“数据流目标”中拖拽OLE DB目标。

在映射中确认一下输入和目标列是否对应好，同时要确认一下数据类型是否一样。

调试包：

点击调试，如果全变绿就哦了，某一个环境出现错误会变成红色，可在执行结果中查看错误原因。

执行结果：

　　select * from userinfo

实例学习SSIS（二）--使用迭代

导读：

实例学习SSIS（一）--制作一个简单的ETL包

实例学习SSIS（二）--使用迭代

实例学习SSIS（三）--使用包配置

实例学习SSIS（四）--使用日志记录和错误流重定向

实例学习SSIS（五）--理论介绍SSIS

目标：循环地连接某文件夹下的每个文件，不需要为每个文件都建立连接管理器。

在这个实例中，我们将某文件夹下的userinfo1.txt和userinfo2.txt的内容都写入数据表userinfo中。

步骤：

准备工作：

把第一个例子中的userinfo.txt复制两份，放到同一个文件夹下。把内容改一下：

设置Foreach：

（1）在控制选项卡下从左侧拖拽Foreach 循环容器。

（2）在Foreach容器的“集合”下设置Enumerator和配置枚举器：

（3）设置枚举器映射为用户定义的变量。

（4）将数据流任务拖拽到Foreach中。

设置平面文件连接管理器：

（1）在“连接管理器”窗格中，单击 userinfo。

（2）在“属性”窗口中，单击 “Expressions (…)”。

（3）在 “属性”列中，选择 ConnectionString。

（4）在“表达式”列中，单击省略号按钮“(…)”。

（5）在“表达式生成器”对话框中，展开“变量”节点，将变量 User::varFileName 拖到“表达式”框中。

到这就哦了。

调试包：

全都绿了吧！

执行结果：

两个文件中的数据都被插入到表userinfo中了！

　　select * from userinfo

实例学习SSIS（三）--使用包配置

导读：

实例学习SSIS（一）--制作一个简单的ETL包

实例学习SSIS（二）--使用迭代

实例学习SSIS（三）--使用包配置

实例学习SSIS（四）--使用日志记录和错误流重定向

实例学习SSIS（五）--理论介绍SSIS

包配置是干嘛滴！

使用包配置可以从开发环境的外部设置运行时属性和变量。

目标：

从开发环境外部指定Foreach要遍历的文件夹，然后把该文件夹下相应的txt文件内容写入库中。

步骤：

准备工作

把第一个例子中的userinfo.txt复制两份，放到同一个文件夹下。把内容改一下：

创建用户变量

添加用户变量以便对应到文件夹名称。

切换到“控制流”选项卡：

将 varFolderName 变量的数据类型设置为“字符串”。

设置Foreach容器

将Foreach容器的Directory对应到刚才创建的变量varFolderName。

上图中的用户变量varFolderName直接拖拽到表达式中即可！

启用包配置

（1）在SSIS菜单，点击“包配置”；

（2）生成配置文件，按下图设置：

配置文件设置

修改配置文件myconfig.dtsConfig\ConfiguredValue的值，如下：

// myconfig.dtsConfig

调试包：

全都绿了就表示数据添加成功了！

执行结果：

文件夹D:\lyp\SQL\SSIS\package3下的userinfo3.txt和userinfo4.txt数据都被插入到表userinfo中了！

select * from userinfo

前面的内容：

实例学习SSIS（一）制作一个简单的ETL包

实例学习SSIS（二）使用迭代

包配置是干嘛滴！

使用包配置可以从开发环境的外部设置运行时属性和变量。

目标：

从开发环境外部指定Foreach要遍历的文件夹，然后把该文件夹下相应的txt文件内容写入库中。

步骤：

准备工作

把第一个例子中的userinfo.txt复制两份，放到同一个文件夹下。把内容改一下：

创建用户变量

添加用户变量以便对应到文件夹名称。

切换到“控制流”选项卡：

将 varFolderName 变量的数据类型设置为“字符串”。

设置Foreach容器

将Foreach容器的Directory对应到刚才创建的变量varFolderName。

上图中的用户变量varFolderName直接拖拽到表达式中即可！

启用包配置

（1）在SSIS菜单，点击“包配置”；

（2）生成配置文件，按下图设置：

配置文件设置

修改配置文件myconfig.dtsConfig\ConfiguredValue的值，如下：

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

> <? xml version="1.0" ?> < DTSConfiguration >< DTSConfigurationHeading >< DTSConfigurationFileInfo GeneratedBy ="LIYANPING\Administrator" GeneratedFromPackageName ="Package3" GeneratedFromPackageID ="{DEED0EEF-48A2-4371-A1FB-C9344691989B}" GeneratedDate ="2009-10-16 14:31:04" /></ DTSConfigurationHeading >< Configuration ConfiguredType ="Property" Path ="\Package.Variables[用户::varFolderName].Properties[Value]" ValueType ="String" >
< ConfiguredValue > D:\lyp\SQL\SSIS\package3 </ ConfiguredValue ></ Configuration ></ DTSConfiguration >

调试包：

全都绿了就表示数据添加成功了！

执行结果：

文件夹D:\lyp\SQL\SSIS\package3下的userinfo3.txt和userinfo4.txt数据都被插入到表userinfo中了！

select * from userinfo

<-->

文本文件

SQL Server Profiler

Windows 事件日志

SQL Server

XML 文件

准备工作

使用上节的包，修改myconfig.dtsConfig，将文件夹对应到D:\lyp\SQL\SSIS\package4。

在文件夹下新建userinfo5.txt和userinfo6.txt。

步骤：

1、添加一条文本日志，如下图：

2、指定写入日志信息的txt文件，如下图：

3、选择要记录的事件：

调试：

在D:\lyp\SQL\SSIS\package4下创建了一个log.txt文件,可以看到里面有相应的事件执行记录。

二、使用错误流重定向

SSIS错误处理方式

在数据转换时很有可能会发生错误，SSIS对错误的处理方式：

1、选择忽略某些列中的失败；

2、重定向整个失败的行；

3、使组件失败。

默认情况下，所有组件发生错误时失败，从而导致包失败并停止后续处理。

为了不让包停止，发生错误时，通过配置来处理错误，通常是将失败的行重定向到别处进行处理。

准备工作

1、使用上面日志记录用到的包；

2、创建有错误的数据源文件userinfo7.txt（D:\lyp\SQL\SSIS\package5\）。

在转换id为int时会出现错误。

使用组件失败的情况

在“数据流”中编辑“用户来源”平面文件源，在“错误输出”中将各列的“错误”选成“组件失败”：

调试程序，可以看到“用户来源”变成了红色，整个包停止了，文件中的数据没有被成功导入到数据库中。

使用错误流重定向

目标

把出错的行重定向的别的文件(errorLog.txt)而不会使包停止。

步骤：

1、拖拽一个“平面文件目标”到数据流选项卡。

2、把“用户来源”的红箭头拖到该目标上，在弹出的“配置错误输出”中将UiD的“错误”选择为“重定向行”。

3、编辑“平面文件目标”：

新建“平面文件链接管理器”，选择错误记录文件（errorLog.txt），如下：

调试：

调试程序，可以看到向数据库中插入了两条记录，并在errorLog.txt中写入了出错的行。

Integration Services 包括：

a)生成并调试包的图形工具和向导；

b)执行如 FTP 操作、SQL 语句执行和电子邮件消息传递等工作流功能的任务；

c)用于提取和加载数据的数据源和目标；

d)用于清理、聚合、合并和复制数据的转换；

e)管理服务，即用于管理 Integration Services 包的 Integration Services 服务；

f)用于对 Integration Services 对象模型编程的应用程序接口 (API)。

二、SSIS体系结构

SSIS由四大部分组成：服务、对象模型、运行时和数据流。

下面这张大图显示了各部分之间的关系：

服务

在Configuration Manager中可以看到SSIS的服务：SQL Server Integration Services。

提示：

1、设计和执行IS包不需要启动该服务，可以使用 SQL Server 导入和导出向导、SSIS 设计器、执行包实用工具以及 dtexec 命令提示实用工具运行包。

2、如果要通过SQL Server Management Studio监视包，则需要启动该服务。

使用SSMS监视时，可以看到两个顶级文件夹：“正在运行的包”和“已存储的包”。

在“正在运行的包”文件夹下可以停止某个运行的包。

对象模型

对象模型包括用于访问 Integration Services 工具、命令行实用工具以及自定义应用程序的本机和托管应用程序编程接口 (API)。

工具介绍：

Business Intelligence Development Studio（BIDS）

创建和调试包。

BIDS中的设计器

如下图：

命令

dtexec:运行现有的包；

如执行包：dtexec /f "c:\pkgOne.dtsx"

dtutil:可以对包进行访问，复制、删除、移动和签名等；

如复制包：dtutil /FILE c:\myTestedPackage\package.dtsx /DestServer myserver /COPY SQL;newpackage

运行时

包的运行时，为日志记录、断点、配置、连接和事务提供支持。

数据流

数据流任务封装数据流引擎。数据流引擎提供将数据从源移动到目标的内存中的缓冲区，并且调用从文件和关系数据库中提取数据的源。

三、典型用途

合并来自异类数据存储区的数据

合并存储在不同数据存储系统中的数据，提取这些数据合并到单个一致的数据存储系统中。

填充数据仓库和数据集市

数据仓库和数据集市中的数据具有更新频繁和加载量大的特点，SSIS专门提供了一个从平面文件大容量加载到 SQL Server的任务。

清除数据和数据标准化

Integration Services 包含一些内置转换，可将其添加到包中以清理数据和将数据标准化、更改数据的大小写、将数据转换为不同类型或格式或者根据表达式创建新列值。

将商业智能置入数据转换过程

Integration Services 提供了用于将商业智能置入 SSIS 包的容器、任务和转换。

可能需要根据数据值对数据进行汇总、转换和分发,SSIS 包中的逻辑可能需要执行以下类型的任务：

a)合并来自多个数据源的数据。

b)计算数据并应用数据转换。

c)根据数据值将一个数据集拆分为多个数据集。

d)将不同的聚合应用到一个数据集的不同子集。

e)将数据的子集加载到不同目标或多个目标。

使管理功能和数据加载自动化

管理功能自动化，例如备份和还原数据库等，可以使用 SQL Server 代理作业安排SSIS 包。

参考：ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/extran9/html/c4398655-5657-4ae4-a690-a380790fe84f.htm　　

作者：青羽

tenghoo.cnblogs.com

caolaosanahnu

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
实例学习SSIS（一）--制作一个简单的ETL包

http://www.cnblogs.com/tenghoo/archive/2009/10/archive/2009/10/archive/2009/10/16/ssis_lookup.html导读：实例学习SSIS（一）--制作一个简单的ETL包实例学习SSIS（二）--使用迭代实例学习SSIS（三）--使用包配置实例学习SSIS（四）--使用日志记录和错误流重
复制链接

扫一扫