SQLServerSSIS学习指南.docx

SQLServerSSIS学习指南.docx

《SQLServerSSIS学习指南.docx》由会员分享，可在线阅读，更多相关《SQLServerSSIS学习指南.docx（116页珍藏版）》请在冰豆网上搜索。

SQLServerSSIS学习指南

SSIS

SSIS其全称是SqlServerIntegrationServices，是Microsoft BI解决方案的一大利器.

SSIS的体系结构主要由四部分组成：

IntegrationServices服务、IntegrationServices对象模型、IntegrationServices运行时和运行时可执行文件以及封装数据流引擎和数据流组件的数据流任务（如图）：

  这是我们初学者必须要了解的，只要明白了这个体系统结构，体会了各组成部分之间的关系，清楚了什么是控制流、什么是数据流，SSIS学起来就不难了。

接下来让我们从一个例子来初探一下SSIS的使用.当然要想成功执行示例.你需要在安装SqlServer2008的时候,选中安装IntegrationServices和 BusinessIntelligenceDevelopmentStudio选项。

这样SSIS开发平台将于一并安装起来。

1.单击开始->所有程序-〉SqlServer2008-〉SQLServerBusinessIntelligenceDevelopmentStudio。

第一次打开这个开发环境的时候可能需要等待几分钟。

该开发工具和VS开发工具很相似。

2.打开BIDS以后，我们可以单击File->新建-〉项目。

如图

3.在“新建项目”对话框的“模板”窗格中，选择“IntegrationServices项目”。

然后在“名称”框中，将默认名称修改为自己的所需的名称。

这个设计如下。

 4.单击“确定”按钮。

默认情况下，将创建一个名为Package.dtsx的空包。

 5.创建好一个SSIS项目后，我们就可以在Package.dtsx包中的控制流、数据流等选项卡中托放控件来设计SSIS包。

到此。

我们了解了SSIS体系结构以及如何利用BIDS创建SSIS包。

以后系列文章，我将逐步介绍各个控件的使用。

SSIS包的开发

在上一章节中我们初步了解了SSIS体系结构以及如何创建一个SSIS包。

现在就介绍一下如何在创建的包中使用各个选项卡。

打开上一章节创建的SSIS包。

整个界面风格如下:

在整个包中包含了控制流选项卡、数据流选项卡、事件处理程序选项卡等等。

下面通过一个简单的例子介绍如何利用这些选项卡创建一个完成的工作任务包。

本示例的主要功能是从数据库AdventureWorks中Product表中读取2001年到2007年的数据导入到Excel中。

1.创建变量

从图左边的变量或从SSIS菜单中选择变量来的打开变量（Variables）按图创建两个日期变量

再图中,我们将变量StartDate的值设置为"2001/1/1",EndDate的值设置为"2007/1/1"

2.创建连接管理器

　　在创建完变量后，接着创建一个连接管理器。

右击程序包编辑器底部的连接管理器窗口的空白处，选择新建OLEDB连接，弹出如下对话框

在图中选择新建按钮。

弹出如下图信息。

然后根据图中的选项配置数据库信息。

最后单击确定保存该连接。

然后在单击确定按钮。

最后在连接管理器上创建一个如下数据库连接

你可以右击新建的LocalHost.AdventureWorks.sa连接管理器并重命名为AdventureWorks。

然后右击程序包编辑器底部的连接管理器窗口-〉新建连接-〉Excel文件的连接管理器-〉添加按钮。

弹出对话框。

然后在对话框中做如下设置。

最终单击确定按钮。

然后修改名称为Product.

3.使用控制流

控制流选项卡是执行程序包的工作流的地方。

在本示例中我们从工具中将数据流任务（DataFlowTask）控件拖放到控制流选项卡中。

如图所示。

这个DataFlowTask将执行数据的转换工作。

4.使用数据流

 双击数据流任务（DataFlowTask）就会打开数据流（DataFlow）选项卡。

下图显示了程序包中的数据流选项卡，尚未拖放控件。

从工具箱中拖出一个OLEDB源，命名为Products。

双击改源进行配置，如图所示。

确保指向AdventureWorks连接管理器。

并将数据访问模式项改为SQL命令。

在SQL命令文本窗口中输入下列命令。

select*fromProduction.Product

whereSellStartDate>?

andSellStartDate

问号表示通过变量传递的参数值。

单击参数按钮将问号映射到已创建的变量。

在设置查询参数窗口中看到每个问号参数都是有序的。

因此第一问号对应Parameter0，第二个问号对应Parameter1。

依次类推。

了解参数配置后，我们将Parameter0映射到User:

:

StartDate,Parameter1映射到User:

:

EndDate。

如图：

单击确定按钮返回到数据流选项卡中。

接下来在工具箱中的数据流目标中将Excel目标控件拖放到设计窗格上。

通过从数据源拖动绿色箭头到该Excel目标源。

然后右键单击Excel目标源选择编辑，弹出对话框，接着按图下步骤设置信息。

设置成功后，整个数据流选项卡的流程如下：

5.执行包

包创建成功后，现在我们来执行一下，看看效果如何。

右键单击Package.dtsx包-〉选择执行包选项。

将会看到如下结果图

途中绿色表示包创建成功并执行完成。

打开导入的Excel，我们发现已经存在数据。

到此一个简单的包流程设计完成。

后续我们将如何使用包中各个选项卡。

SSIS包的组建之连接管理器

上一篇我们通过一个示例来介绍一下SSIS包的开发.接下来的内容我们将学习一下包中各个选项卡的使用.如:

连接管理器选项卡、控制流选项卡、数据流选项卡和事件处理选项卡等等。

这一篇将介绍一下连接管理器作用以及使用情况。

连接管理器的作用是连接到不同类型的数据源以提取和加载数据。

任何包的开发都需要提供源数据。

下表列出了SQLServerIntegrationServices提供的连接管理器类型。

类型

说明

ADO

连接到ActiveX数据对象（ADO）对象。

ADO.NET

使用.NET提供程序连接到数据源。

CACHE

从数据流或从缓存文件（.caw）中读取数据，并可将数据保存到缓存文件。

EXCEL

连接到Excel工作簿文件。

FILE

连接到文件或文件夹。

FLATFILE

连接到单个平面文件中的数据。

FTP

连接到FTP服务器。

HTTP

连接到Web服务器。

MSMQ

连接到消息队列。

MSOLAP100

连接到SQLServerAnalysisServices实例或AnalysisServices项目。

MULTIFILE

连接到多个文件和文件夹。

MULTIFLATFILE

连接到多个数据文件和文件夹。

OLEDB

使用OLEDB访问接口连接到数据源。

ODBC

使用ODBC连接到数据源。

SMOServer

连接到SQLServer管理对象（SMO）服务器。

SMTP

连接到SMTP邮件服务器。

SQLMOBILE

连接到SQLServerCompact数据库。

WMI

连接到服务器，并指定服务器上WindowsManagementInstrumentation（WMI）管理的范围。

下面就简单的介绍一下我们常用的使用连接管理器类型。

∙ OLEDB访问接口连接到数据源（数据库连接）

1.首先打开上一篇创建名为"SSISDemo"项目.

2.在连接管理器窗口中，右击选择NewOLEDBConnnection项，将弹出如下窗口:

左边数据连接框显示的连接是我们已创建好的数据库连接。

右边数据库连接属性框显示对应的属性信息。

你也可以新建一个数据库连接。

单击新建按钮，弹出如下对话框

在这对话框中，我们可以根据自己的需要填写相应的内容。

本例是以连接本地AdventureWorks数据库。

单击确定按钮。

则会在连接管理器看到如下信息:

图中LocalHost.AdventureWorks.sa就是我们刚才创建的数据库连接。

右击LocalHost.AdventureWorks.sa弹出属性框。

可以根据需要修改这些属性。

比如将Name属性修改为:

AdventureWorks 。

 到此一个简单OLEDB连接方式建立完成。

其中的一些属性含义。

自己可以在后续的学习中逐步的掌握。

∙ FILE连接到文件或文件夹（平面文件连接）

  平面文件连接管理器要比OLEDB连接方式要复杂的多。

平面文件连接方式主要是连接非数据库类型的文件。

下面介绍下如何创建平面文件连接。

假如我们有一个名为User.txt文件。

数据格式如下:

每行有两列数据。

已“Tab”隔开。

接下来介绍如何在连接管理器中连接这个文本文件。

1.仍然在连接管理器框中右击，选中新建平面文件连接，弹出对话框

在图中可以看到配置平面文件连接管理器需要配置连接管理器名称、常规、列、高级、预览这个属性。

下面将介绍如何配置这些属性

▪连接管理器名称:

为工作流中的平面文件连接提供唯一的名称。

所提供的名称将在连接管理器框中显示。

▪常规选项卡:

使用“平面文件连接管理器编辑器”对话框的“常规”页可以选择文件和数据格式。

使用平面文件连接可以将包连接到文本文件。

该选项卡中包含以下属性：

文件名:

键入要在平面文件连接中使用的路径和文件名。

区域设置:

在区域设置下拉框中选中指定的区域位置，以便为排序以日期和时间格式提供语言特性的信息。

在设计的时候最好选择英语（美国）选项

Unicode复选框:

指示是否使用Unicode。

如果使用Unicode则不能指定代码页。

代码页：

在代码页中选中指定非Unicode文本的代码页。

设计时最好选择1252（ANSI-拉丁语I）格式:

在格式下拉框中选中文本的格式

属性　　

说明

带分隔符

各列之间由在“列”页上指定的分隔符隔开

固定宽度

列的宽度固定

右边未对齐

在右边未对齐的文本中，除最后一列之外的每一列的宽度都相同。

它有行分割符分割

　　　文本限定符:

指定要使用的文本限定符。

例如，可以指定文本字段必须用引号括起来。

若选择文本限定符之后，就不能重新选择"无"选项，键入None以取消选择文本限定　　　　  　　　　　　　符。

　　　标题行分隔符：

从标题行的分隔符列表中选择，或输入分隔符文本。

值

说明

{CR}{LF}

标题行由回车符和换行符的组合分隔。

{CR}

标题行由回车符分隔。

{LF}

标题行由换行符分隔。

分号{;}

标题行由分号分隔。

冒号{:

}

标题行由冒号分隔。

逗号{,}

标题行由逗号分隔。

制表符{t}

标题行由制表符分隔。

竖线{|}

标题行由竖线分隔。

　　要跳过的标题行数：

指定要跳过的标题行数或初始数据行数（如果有的话）。

　　在第一个数据行中显示列名称：

指示在第一个数据行中是否要求列名或提供列名。

根据常规选项卡中的这些属性以后，我们就可以根据需求设定文本显示的格式。

如下图

▪列选项卡：

使用”平面文件连接管理器编辑器”对话框中的”列”选项卡可以在这里设置行和列的信息。

并预览相应的文件。

如下图:

包含如下属性

行和列分隔符:

此属性和常规选项卡中的标题行分隔符一样。

可以根据需求设置行和列的显示方式。

预览:

查看平面文件中的示例数据，这些数据已按所选的选项划分为列和行.如上图。

刷新:

通过单击“刷新”查看更改要跳过的分隔符后的效果。

只有在更改行或列选项之后，此按钮才可见。

重置列:

通过单击“重置列”可以删除除原始列之外的所有列。

只有调转到其他选项卡后，然后再回到“列”选项卡，此按钮才可见。

本示例我们在列分隔符选项中选择制表符（t）,然后单击刷新按钮。

▪高级选项卡：

使用“平面文件连接管理器编辑器”对话框的“高级”页，设置指定IntegrationServices如何读写平面文件中的数据的属性。

可以更改平面文件中各个列的名称，并设置包括文件中每个列的数据类型和分隔符在内的属性。

默认情况下，字符串列的长度为50个字符。

可以调整这些列的长度，以免数据截断或超出列宽。

还可以更新其他元数据以便与目标列兼容。

例如，可以将只包含整型数据的列的数据类型更改为数值数据类型，例如DT_I2。

可以手动进行这些修改，也可以单击“选择类型”按钮，以使用“提供列类型建议”对话框来评估示例数据并自动进行其中一些更改。

在高级选项卡中我们可以做如下工做：

　　配置各列的属性：

选择左窗格中的列可在右窗格中查看列的属性。

请参阅下表以了解数据类型属性的说明。

列出的部分属性仅对某些平面文件格式是可配置的。

属性

说明

ColumnType

表示列是由分隔符分隔、还是固定宽度，或是右边未对齐。

此属性是只读的。

在右边未对齐的文件中，除最后一列之外的每一列的宽度都固定。

它由行分隔符分隔。

OutputColumnWidth

指定值存储为字节数；对于Unicode文件，此值对应于字符数。

在数据流任务中，此值用于设置平面文件源的输出列宽。

注意

在对象模型中，此属性的名称为MaximumWidth。

DataType

从可用数据类型的列表中进行选择。

TextQualified

指示文本数据周围是否有文本限定符（例如引号字符）。

值说明

True平面文件中的文本数据是受限定的。

False平面文件中的文本数据是不受限定的。

Name

提供说明性列名。

如果不输入名称，则IntegrationServices将自动创建名称，格式为“列0”、“列1”，依此类推。

DataScale

指定数字数据的小数位数。

小数位数是指小数点后的位数。

ColumnDelimiter

从可用列分隔符的列表中进行选择。

选择不可能出现在文本中的分隔符。

对于固定宽度的列，将忽略此值。

值说明

{CR}{LF}列由回车符和换行符的组合分隔。

{CR}列由回车符分隔。

{LF}列由换行符分隔。

分号{;}列由分号分隔。

冒号{:

}列由冒号分隔。

逗号{,}列由逗号分隔。

制表符{t}列由制表符分隔。

竖线{|}列由竖线分隔。

DataPrecision

指定数字数据的精度。

精度是指数字的位数。

InputColumnWidth

指定值以字节数进行存储；对于Unicode文件，该值将显示为字符数。

对于分隔列，将忽略此值。

注意在对象模型中，此属性的名称为ColumnWidth。

　　新建：

通过单击“新建”添加一个新列。

默认情况下，单击“新建”按钮将会在列表末尾添加新列。

该按钮还包括以下选项，可以在下拉列表中选择。

值

说明

添加列

在列表末尾添加新列。

在其前插入

在所选列前面插入新列。

在其后插入

在所选列后面插入新列。

　　删除：

选择一列，然后单击“删除”来删除该列。

　　建议类型：

使用“提供列类型建议”对话框可以计算文件中的示例数据，并获取关于每列的数据类型和长度的建议。

充分了解高级选项卡的属性后，我们将列0的Name和DataTye属性改为CustomerID和DT_I8类型。

将列1的Name和DataTye属性改为SKUList和DT_STR类型，并将OutColumnWidth属性修改为1000。

▪浏览选项卡：

单击浏览选项卡，可以看到如下图显示的数据

只要前三个选项卡配置成功后，预览一下数据显示格式就可以了。

最后单击确定按钮。

平面文件连接管理器就建立成功。

最终在连接管理器框中显示刚才创建名为“Userout.txt“的平面文件管理器。

本节就介绍常用的两个连接管理器。

其他的连接管理器可以参考这两个例子自己尝试去建立连接。

SSIS的控制流之For循环容器

SSIS包由一个控制流以及一个或多个数据流（可选）组成。

下面的关系图显示具有一个容器和六项任务的控制流。

这些任务中有五项定义于包级别，还有一项定义于容器级别。

任务位于容器内。

在控制流中的工具箱.我们可以将工具箱中的控件分为容器控件和任务控件两类。

现在我们先了解一下容器包含哪些控件

容器

1.For循环

For循环容器类似于编成语言中的For循环结构。

循环每次重复时，For循环容器都计算一个表达式并重复运行其循环，直到表达式计算结果为False才结束次循环。

For容器使用很简单。

这里就简单介绍一下使用方法。

>BIDS开发工具打开创建的“SSISDemo”项目，然后创建一个新的包。

命名为“ForContainerDemo”

>在工具左边选中变量选项卡，如果没有该选项，则可以在菜单上选中”SSIS”菜单,然后单击变量。

弹出如下图:

在这里我们可以自己创建变量。

单击上图中的第一个图标。

创建一个变量。

创建好后，可以设置其数据类型，初始值。

结果如下图：

你也可以单击第二个图标，删除其变量。

>选中控制流选项卡后，在左边将出现工具选项卡，打开选项卡，将For循环容器拖到控制流中。

然后再将一个脚本任务控件拖放到For容器中如图:

>右击For容器，单击编辑，弹出对话框

我们将看到如下属性

InitExpression:

提供初始化该循环所用值的表达式（可选）。

EvalExpression:

提供用于计算循环应停止还是继续的表达式。

AssignExpression:

提供在每次循环重复时更改条件的表达式（可选）。

Name:

为For循环容器提供唯一的名称。

此名称用作任务图标中的标签。

Description:

提供For循环容器的说明。

依据这些属性按如下图设置。

其中Count变量就是我们在变量容器中设置的变量，在使用变量时在其前面加上“@”。

单击确定按钮即可

>拖放一个脚本任务控件到For容器中，然后右击脚本任务-〉选择编辑。

弹出对话框。

在ReadOnlyVariables属性中单击右边的“…”按钮，弹出选择变量对话框。

在这对话框中我们选中自己定义的Count变量。

配置结果如下:

>接着单击图上的编辑脚本按钮。

弹出VS编辑器。

在main函数中加入如下代码:

stringCount=Dts.Variables["Count"].Value.ToString（）;

           MessageBox.Show（Count）;

然后关闭VS编辑器。

最后关闭脚本任务编辑器

>右键单击“ForContainerDemo”包，选择执行包，结果如图:

我们发现弹出五次对话框。

说明For容器循环了5次。

最终执行结果如下。

到此，For循环容器控件介绍完成。

接着我们介绍下一个Foreach循环容器。

相信大家看到Foreach字词。

估计也猜出如何使用该控件了吧。

SSIS的控制流之Foreach循环容器和序列容器

上一篇介绍了For循环容器的使用。

本篇将介绍Foreach循环容器和序列容器的使用。

Foreach循环容器

Foreach循环容器定义包中的控制流。

其循环的实现类似于编程语言中的Foreach循环结构。

SQLServiceIntegrationServices为Foreach容器提供了一下几种枚举类型

枚举器

配置要求

ForeachADO

指定ADO对象源变量和枚举器模式。

ForeachADO.NET架构行集

指定与数据库的连接和要枚举的架构。

Foreach文件

指定要枚举的文件夹和文件、检索到的文件的文件名格式，以及是否要遍历子文件夹。

Foreach源变量

指定包含要枚举的对象的变量。

Foreach项

定义Foreach项集合中的项，包括列和列数据类型。

ForeachNodelist

指定XML文档的源并配置XPath操作。

ForeachSMO

指定与数据库的连接以及要枚举的SMO对象。

下面看看具体如何使用该控件。

假如我们在D:

\SampleData含有如下文件:

现在我们用Foreach容器遍历这些文件。

>创建一个名为”ForeachContainerDemo”的包。

然后定义一个变量FileName，类型设置为String。

>按如下图拖放控件

>右击”遍历货币”单击编辑。

按如下图配置信息

其中文件属性中设置".txt"表示读取指定目录下的所有txt文件。

然改为a.txt表示读取目录下指定的文件。

>单击变量映射选项,配置如下:

该配置表示，每次遍历的文件名赋值给变量FileName.然后点击确定。

至此Foreach容器配置完毕。

>配置脚本任务和上一篇配置相同。

只是做如下改动即可。

修改ReadOnlyVariables属性值为“User:

:

FileName”,并在Main函数中写下如下代码。

 stringfileName=Dts.Variables["FileName"].Value.ToString（）;

  MessageBox.Show（fileName）;

>执行包。

将遍历所以文本文件。

结束后的效果如下:

序列容器

序列容器是一种十分简单，但使用非常广泛的容器，它可以对控制流的任务流组件进行结构化处理，把一些业务相关的任务组件，放到一个序列容器中，使整个包看起来更加整洁、美观，就如同我们家里的书柜、衣柜似的，把不同种类的东西整理在里面，收藏起来，既美观，又易于取用。

它是将一个或多个控件作为一个工作单元来处理。

例如:

在图中名为“PrepareNewDataBase”的序列容器包含三个执行Sql任务的控件。

是将这三个控件组合成一个控件。

只有三个控件都执行成功后，才能继续执行下面的流程。

序列容器的作用就是这些。

不需要配置，也不要编写代码。

只要将控件拖放到序列容器即可。

有点类似我们C#中用户控件一样。

到此本篇的内容就结束。

主要是介绍For容器控件的使用。

顺便介绍一下序列容器控件。

SSIS之序列容器

 序列容器是一种十分简单，但使用非常广泛的容器，它可以对控制流的任务流组件进行结构化处理，把一些业务相关的任务组件，放到一个序列容器中，使整个包看起来更加整洁、美观，就如同我们家里的书柜、衣柜似的，把不同种类的东西整理在里面，收藏起来，既美观，又易于取用。

　　比如，在一个IntegrationSevices包中，包含有财务数据、业务数据。

整个包中的任务组件会非常多，如果不对它们进行分组管理，整个包看起来就会比较乱，非常难于管理与维护。

相反如果我们将处理财务数据的任务，放入一个序列容器中，将处理业务数据的任务放入另一个序列容器，整个包看起来，就会一目了然，哪些任务是处理财务数据，哪些任务是处理业务数据了，非常清楚。

如图：

 　不仅如此，如果点击右上角象箭头一样的符号，还可才把这些容器折叠起来，整个包就更加清楚明了，一目了然了。

如图：

SSIS之数据流任务

 数据流任务主要用于运行数据流以提取、转换、加载源数据。

一个包中可以有多个数据流任务，也可以包含零个数据流任务。

下面关系图显示含有一个数据流任务的包：

了解数据流任务后，我们就简要的学习一下如何创建数据流任务。

1.在工具箱中双击数据流任务控件或则将其拖放到控制流页面中。

如图:

你可以