| 订阅 | 在线投稿
分享
 
 
 

SSIS处理导入数据时, 存在的更新, 不存在的插入

来源:互联网网民  宽屏版  评论
2006-09-15 06:28:06

问题描述:

当你把数据从其他数据库, 或者是文本文件之类的其他数据源导入到目的数据库时, 有时希望在导入的处理中, 能够实现"数据存在时更新, 不存在时导入"

在之前, 一般是通过导入临时表, 然后再判断处理导入正式表的, 在SQL Server 2005中, SSIS可以在导入处理时直接完成这种处理.

下面具体演示一下如何用SSIS完成这样的处理:

1. 准备测试环境

-- 1. 在数据库中创建下面的对象

USE tempdb

GO

CREATE TABLE dbo.tb(

id int PRIMARY KEY,

name nvarchar(128))

GO

-- 2. 准备两个文本文件, 放在d:\test 目录下, 文件的内容如下

t1.txt

id name

1 张三

2 李四

t2.txt

id name

1 张三君

3 李林

4 阿联酋

2. 创建新的 Integration Services 项目(创建SSIS包)

Ø 在“开始”菜单中,依次指向“所有程序”、“Microsoft SQL Server 2005”,再单击 SQL Server Business Intelligence Development Studio。

Ø 在“文件”菜单中,指向“新建”,再单击“项目”,以创建一个新的 Integration Services 项目。

Ø 在“新建项目”对话框的“模板”窗格中,选择“Integration Services 项目”。

Ø 在“名称”框中,将默认名称更改为 SSIS Tutorial。或者,清除“创建解决方案的目录”复选框。

Ø 接受默认位置,或单击“浏览”,以浏览并找到要使用的文件夹。

Ø 在“项目位置”对话框中,单击文件夹,再单击“打开”。

Ø 单击“确定”。

Ø 默认情况下,将创建一个名为 “新建包.dtsx“ 的空包,并将该包添加到项目中。

Ø 在解决方案资源管理器工具栏中,右键单击 Package.dtsx,再单击“重命名”,将默认包重命名为 Lesson 1.dtsx。

Ø 当系统提示重命名包对象时,单击“是”。

3. 为SSIS包添加数据源(导入数据的源和目标数据源)

Ø 首先添加导入数据的源

Ø 右键单击“连接管理器”区域中的任意位置,再单击“新建平面文件连接”。

Ø 在“平面文件连接管理器编辑器”对话框的“连接管理器名称”字段中,键入 Source。

Ø 单击“浏览”。

Ø 在“打开”对话框中,浏览并找到“d:\test\t1.txt”文件。

Ø “常规”选项中,勾选“在第1个数据行中显示列名称”。

Ø “高级”选项中,选择“id”列,将数据类型设置为“four-byte single integer[DT_I4]”。

Ø “高级”选项中,选择“name”列,将数据类型设置为“Unicode string[DT_WSTR]”。

Ø 然后,你可以在“预览”中查看数据是否正确。

Ø 然后添加接收数据的目的数据源

Ø 右键单击连接管理器区域中的任意位置,再单击“新建OLE DB 连接”。

Ø 在“配置OLE DB 连接管理器”对话框中,单击“新建”。

Ø 在“服务器名称”中,输入localhost。

Ø 将localhost 指定为服务器名称时,连接管理器将连接到本地计算机上Microsoft SQL Server 2005 的默认实例。若要使用SQL Server 2005 的远程实例,请将localhost 替换为要连接到的服务器的名称。

Ø 在“登录到服务器”组中,确认选择了“使用Windows 身份验证”。

Ø 在“连接到数据库”组的“选择或输入数据库名称”框中,键入或选择tempdb。

Ø 单击“测试连接”,验证指定的连接设置是否有效。

Ø 单击“确定”。

Ø 单击“确定”。

Ø 在“配置OLE DB 连接管理器”对话框的“数据连接”窗格中,确认选择了localhost.tempdb。

Ø 单击“确定”。

4. 为SSIS包添加数据流任务

Ø 单击“控制流”选项卡。

Ø 在“工具箱”中,展开“控制流项”,并将一个数据流任务拖到“控制流”选项卡的设计图面上。

Ø 在“控制流”设计图面中,右键单击新添加的数据流任务,再单击“重命名”,将名称更改为Import Data。

5. 在数据流任务中设置数据流源

Ø 打开“数据流”设计器,方法是双击Import Data 数据流任务或单击“数据流”选项卡。

Ø 在“工具箱”中,展开“数据流源”,然后将“平面文件源”拖动到“数据流”选项卡的设计图面上。

Ø 在“数据流”设计图面上,右键单击新添加的“平面文件源”,单击“重命名”,然后将该名称更改为Source Data。

Ø 双击此平面文件源,打开“平面文件源编辑器”对话框。

Ø 在“平面文件连接管理器”框中,键入或选择Source。

Ø 单击“列”并验证列名是否正确。

Ø 单击“确定”。

6. 在数据流任务中添加查找处理组件

Ø 在“工具箱”中,展开“数据流转换”,然后将“查找”拖动到“数据流”选项卡的设计图面上。将“查找”直接放置在Source Data 源的下面。

Ø 单击Source Data 平面文件源,并将绿色箭头拖动到新添加的“查找”转换中,以连接这两个组件。

Ø 在“数据流”设计图面上,右键单击新添加的“查找”转换,单击“重命名”,然后将该名称更改为Lookup id。

Ø 双击Lookup id 转换。

Ø 在“查找转换编辑器”对话框的“OLE DB 连接管理器”框中,确保显示localhost.tempdb。

Ø 在“使用表或视图”框中,键入或选择[dbo].[tb]。

Ø 单击“列”选项卡。

Ø 在“可用输入列”面板中,将id 拖放到“可用查找列”面板的id 上。

Ø 单击“确定”。

7. 在数据流任务中添加插入数据处理需要的目标数据源

Ø 在“工具箱”中,展开“数据流目标”,并将“OLE DB 目标”拖到“数据流”选项卡的设计图面上。将OLE DB 目标直接放置在“Lookup id”转换的下面。

Ø 单击“Lookup id”转换,并将红色箭头拖到新添加的“OLE DB 目标”上,以便将两个组件连接在一起。

Ø 在出现的“配置错误输出”对话框中,“错误”列中选择“重定向行”

Ø 单击“确定”。

Ø 在“数据流”设计图面上,右键单击新添加的“OLE DB 目标”组件,单击“重命名”,然后将名称更改为Insert data。

Ø 双击Insert data。

Ø 在“OLE DB 目标编辑器”对话框中,确保已在“OLE DB 连接管理器”框中选中localhost.tempdb。

Ø 在“表或视图的名称”框中,键入或选择[dbo].[tb]。

Ø 单击“映射”。

Ø 验证id, name 输入列是否已正确映射到目标列。如果映射了同名列,则说明映射正确。

Ø 单击“确定”。

8. 在数据流任务中添加更新数据处理需要的OLE DB命令组件

Ø 在“工具箱”中,展开“数据流组件转换”,并将“OLE DB 命令”拖到“数据流”选项卡的设计图面上。将OLE DB 目标直接放置在“Lookup id”转换的下面。

Ø 单击“Lookup id”转换,并将绿色箭头拖到新添加的“OLE DB 命令”上,以便将两个组件连接在一起。

Ø 在“数据流”设计图面上,右键单击新添加的“OLE DB命令”组件,单击“重命名”,然后将名称更改为Update data。

Ø 双击Update data。

Ø 在“Update Data 高级编辑器”对话框中,“连接管理”选项的“连接管理器”列中,选中localhost.tempdb。

Ø 在“组件属性”选项中,“自定义属性”的“SQLCommand”属性中输入:

UPDATE dbo.tb SET name = ? WHERE id = ?

Ø 在“列映射”选项中,设置“输入列”,将name映射到param_0,将id映射到param_1。注:param_0对应UPDAT语句中的第1个?,而param_1对应UPDATE语句中的第2个?,这是固定的。

Ø 单击“确定”。

9. 测试

Ø 按“F5”执行SSIS包

Ø 执行结束(所有的组件都变为绿色),你会看到数据流向“Inset Data”的有两条数据

Ø 双击“连接管理器”中的Source,重新设置文件名为D:\test\d2.txt。

Ø 单击“确定”

Ø 按“Ctrl+Shift+F5”,重新启动SSIS

Ø 执行结束(所有的组件都变为绿色),你会看到数据流向“Inset Data”的有两条数据,流向“Update Data”的有1条数据

Ø 最后,在数据库中查询tempdb.dbo.tb,验证数据导入的正确性

10. 添加循环,一次完成test目录下所有文件的导入

Ø 在Business Intelligence Development Studio 中,单击“控制流”选项卡。

Ø 在“工具箱”中,展开“控制流项”,然后将“Foreach 循环容器”拖到“控制流”选项卡的设计图面上。

Ø 右键单击新添加的“Foreach 循环容器”,并选择“编辑”。

Ø 在“Foreach 循环编辑器”对话框的“常规”页中,为“名称”输入Foreach File in Folder。单击“确定”。

Ø 为Foreach 循环容器配置枚举器

Ø 双击文件夹中的Foreach 文件以重新打开“Foreach 循环编辑器”。

Ø 单击“集合”。

Ø 在“集合”页中,选择“Foreach 文件枚举器”。

Ø 在“枚举器配置”组中,单击“浏览”。

Ø 在“浏览文件夹”对话框中,找到d:\test。

Ø 在“文件”框中,键入*.txt。

Ø 单击“变量映射”, 将枚举器映射为用户定义的变量。

Ø 在“变量映射”页的“变量”列中,单击空单元格并选择“<新建变量…>”。

Ø 在“添加变量”对话框中,为“名称”键入varFileName。

Ø 单击“确定”。

Ø 再次单击“确定”,退出“Foreach 循环编辑器”对话框。

Ø 将数据流任务Import Data 数据流任务拖动到现已重命名为Foreach File in Folder 的Foreach 循环容器中。

Ø 配置平面文件连接管理器以使用连接字符串的变量

Ø 在“连接管理器”窗格中,右键单击Source Data,再选择“属性”。

Ø 在“属性”窗口中,针对“表达式”,单击空单元,然后单击省略号按钮“(…)”。

Ø 在“属性表达式编辑器”对话框的“属性”列中,键入或选择ConnectionString。

Ø 在“表达式”列中,单击省略号按钮“(…)”以打开“表达式生成器”对话框。

Ø 在“表达式生成器”对话框中,展开“变量”节点。

Ø 将变量用户::varFileName 拖到“表达式”框中。

Ø 单击“确定”关闭“表达式生成器”对话框。

Ø 再次单击“确定”关闭“属性表达式编辑器”对话框。

 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
问题描述: 当你把数据从其他数据库, 或者是文本文件之类的其他数据源导入到目的数据库时, 有时希望在导入的处理中, 能够实现"数据存在时更新, 不存在时导入" 在之前, 一般是通过导入临时表, 然后再判断处理导入正式表的, 在SQL Server 2005中, SSIS可以在导入处理时直接完成这种处理. 下面具体演示一下如何用SSIS完成这样的处理: 1. 准备测试环境 -- 1. 在数据库中创建下面的对象 USE tempdb GO CREATE TABLE dbo.tb( id int PRIMARY KEY, name nvarchar(128)) GO -- 2. 准备两个文本文件, 放在d:\test 目录下, 文件的内容如下 t1.txt id name 1 张三 2 李四 t2.txt id name 1 张三君 3 李林 4 阿联酋 2. 创建新的 Integration Services 项目(创建SSIS包) Ø 在“开始”菜单中,依次指向“所有程序”、“Microsoft SQL Server 2005”,再单击 SQL Server Business Intelligence Development Studio。 Ø 在“文件”菜单中,指向“新建”,再单击“项目”,以创建一个新的 Integration Services 项目。 Ø 在“新建项目”对话框的“模板”窗格中,选择“Integration Services 项目”。 Ø 在“名称”框中,将默认名称更改为 SSIS Tutorial。或者,清除“创建解决方案的目录”复选框。 Ø 接受默认位置,或单击“浏览”,以浏览并找到要使用的文件夹。 Ø 在“项目位置”对话框中,单击文件夹,再单击“打开”。 Ø 单击“确定”。 Ø 默认情况下,将创建一个名为 “新建包.dtsx“ 的空包,并将该包添加到项目中。 Ø 在解决方案资源管理器工具栏中,右键单击 Package.dtsx,再单击“重命名”,将默认包重命名为 Lesson 1.dtsx。 Ø 当系统提示重命名包对象时,单击“是”。 3. 为SSIS包添加数据源(导入数据的源和目标数据源) Ø 首先添加导入数据的源 Ø 右键单击“连接管理器”区域中的任意位置,再单击“新建平面文件连接”。 Ø 在“平面文件连接管理器编辑器”对话框的“连接管理器名称”字段中,键入 Source。 Ø 单击“浏览”。 Ø 在“打开”对话框中,浏览并找到“d:\test\t1.txt”文件。 Ø “常规”选项中,勾选“在第1个数据行中显示列名称”。 Ø “高级”选项中,选择“id”列,将数据类型设置为“four-byte single integer[DT_I4]”。 Ø “高级”选项中,选择“name”列,将数据类型设置为“Unicode string[DT_WSTR]”。 Ø 然后,你可以在“预览”中查看数据是否正确。 Ø 然后添加接收数据的目的数据源 Ø 右键单击连接管理器区域中的任意位置,再单击“新建OLE DB 连接”。 Ø 在“配置OLE DB 连接管理器”对话框中,单击“新建”。 Ø 在“服务器名称”中,输入localhost。 Ø 将localhost 指定为服务器名称时,连接管理器将连接到本地计算机上Microsoft SQL Server 2005 的默认实例。若要使用SQL Server 2005 的远程实例,请将localhost 替换为要连接到的服务器的名称。 Ø 在“登录到服务器”组中,确认选择了“使用Windows 身份验证”。 Ø 在“连接到数据库”组的“选择或输入数据库名称”框中,键入或选择tempdb。 Ø 单击“测试连接”,验证指定的连接设置是否有效。 Ø 单击“确定”。 Ø 单击“确定”。 Ø 在“配置OLE DB 连接管理器”对话框的“数据连接”窗格中,确认选择了localhost.tempdb。 Ø 单击“确定”。 4. 为SSIS包添加数据流任务 Ø 单击“控制流”选项卡。 Ø 在“工具箱”中,展开“控制流项”,并将一个数据流任务拖到“控制流”选项卡的设计图面上。 Ø 在“控制流”设计图面中,右键单击新添加的数据流任务,再单击“重命名”,将名称更改为Import Data。 5. 在数据流任务中设置数据流源 Ø 打开“数据流”设计器,方法是双击Import Data 数据流任务或单击“数据流”选项卡。 Ø 在“工具箱”中,展开“数据流源”,然后将“平面文件源”拖动到“数据流”选项卡的设计图面上。 Ø 在“数据流”设计图面上,右键单击新添加的“平面文件源”,单击“重命名”,然后将该名称更改为Source Data。 Ø 双击此平面文件源,打开“平面文件源编辑器”对话框。 Ø 在“平面文件连接管理器”框中,键入或选择Source。 Ø 单击“列”并验证列名是否正确。 Ø 单击“确定”。 6. 在数据流任务中添加查找处理组件 Ø 在“工具箱”中,展开“数据流转换”,然后将“查找”拖动到“数据流”选项卡的设计图面上。将“查找”直接放置在Source Data 源的下面。 Ø 单击Source Data 平面文件源,并将绿色箭头拖动到新添加的“查找”转换中,以连接这两个组件。 Ø 在“数据流”设计图面上,右键单击新添加的“查找”转换,单击“重命名”,然后将该名称更改为Lookup id。 Ø 双击Lookup id 转换。 Ø 在“查找转换编辑器”对话框的“OLE DB 连接管理器”框中,确保显示localhost.tempdb。 Ø 在“使用表或视图”框中,键入或选择[dbo].[tb]。 Ø 单击“列”选项卡。 Ø 在“可用输入列”面板中,将id 拖放到“可用查找列”面板的id 上。 Ø 单击“确定”。 7. 在数据流任务中添加插入数据处理需要的目标数据源 Ø 在“工具箱”中,展开“数据流目标”,并将“OLE DB 目标”拖到“数据流”选项卡的设计图面上。将OLE DB 目标直接放置在“Lookup id”转换的下面。 Ø 单击“Lookup id”转换,并将红色箭头拖到新添加的“OLE DB 目标”上,以便将两个组件连接在一起。 Ø 在出现的“配置错误输出”对话框中,“错误”列中选择“重定向行” Ø 单击“确定”。 Ø 在“数据流”设计图面上,右键单击新添加的“OLE DB 目标”组件,单击“重命名”,然后将名称更改为Insert data。 Ø 双击Insert data。 Ø 在“OLE DB 目标编辑器”对话框中,确保已在“OLE DB 连接管理器”框中选中localhost.tempdb。 Ø 在“表或视图的名称”框中,键入或选择[dbo].[tb]。 Ø 单击“映射”。 Ø 验证id, name 输入列是否已正确映射到目标列。如果映射了同名列,则说明映射正确。 Ø 单击“确定”。 8. 在数据流任务中添加更新数据处理需要的OLE DB命令组件 Ø 在“工具箱”中,展开“数据流组件转换”,并将“OLE DB 命令”拖到“数据流”选项卡的设计图面上。将OLE DB 目标直接放置在“Lookup id”转换的下面。 Ø 单击“Lookup id”转换,并将绿色箭头拖到新添加的“OLE DB 命令”上,以便将两个组件连接在一起。 Ø 在“数据流”设计图面上,右键单击新添加的“OLE DB命令”组件,单击“重命名”,然后将名称更改为Update data。 Ø 双击Update data。 Ø 在“Update Data 高级编辑器”对话框中,“连接管理”选项的“连接管理器”列中,选中localhost.tempdb。 Ø 在“组件属性”选项中,“自定义属性”的“SQLCommand”属性中输入: UPDATE dbo.tb SET name = ? WHERE id = ? Ø 在“列映射”选项中,设置“输入列”,将name映射到param_0,将id映射到param_1。注:param_0对应UPDAT语句中的第1个?,而param_1对应UPDATE语句中的第2个?,这是固定的。 Ø 单击“确定”。 9. 测试 Ø 按“F5”执行SSIS包 Ø 执行结束(所有的组件都变为绿色),你会看到数据流向“Inset Data”的有两条数据 Ø 双击“连接管理器”中的Source,重新设置文件名为D:\test\d2.txt。 Ø 单击“确定” Ø 按“Ctrl+Shift+F5”,重新启动SSIS Ø 执行结束(所有的组件都变为绿色),你会看到数据流向“Inset Data”的有两条数据,流向“Update Data”的有1条数据 Ø 最后,在数据库中查询tempdb.dbo.tb,验证数据导入的正确性 10. 添加循环,一次完成test目录下所有文件的导入 Ø 在Business Intelligence Development Studio 中,单击“控制流”选项卡。 Ø 在“工具箱”中,展开“控制流项”,然后将“Foreach 循环容器”拖到“控制流”选项卡的设计图面上。 Ø 右键单击新添加的“Foreach 循环容器”,并选择“编辑”。 Ø 在“Foreach 循环编辑器”对话框的“常规”页中,为“名称”输入Foreach File in Folder。单击“确定”。 Ø 为Foreach 循环容器配置枚举器 Ø 双击文件夹中的Foreach 文件以重新打开“Foreach 循环编辑器”。 Ø 单击“集合”。 Ø 在“集合”页中,选择“Foreach 文件枚举器”。 Ø 在“枚举器配置”组中,单击“浏览”。 Ø 在“浏览文件夹”对话框中,找到d:\test。 Ø 在“文件”框中,键入*.txt。 Ø 单击“变量映射”, 将枚举器映射为用户定义的变量。 Ø 在“变量映射”页的“变量”列中,单击空单元格并选择“<新建变量…>”。 Ø 在“添加变量”对话框中,为“名称”键入varFileName。 Ø 单击“确定”。 Ø 再次单击“确定”,退出“Foreach 循环编辑器”对话框。 Ø 将数据流任务Import Data 数据流任务拖动到现已重命名为Foreach File in Folder 的Foreach 循环容器中。 Ø 配置平面文件连接管理器以使用连接字符串的变量 Ø 在“连接管理器”窗格中,右键单击Source Data,再选择“属性”。 Ø 在“属性”窗口中,针对“表达式”,单击空单元,然后单击省略号按钮“(…)”。 Ø 在“属性表达式编辑器”对话框的“属性”列中,键入或选择ConnectionString。 Ø 在“表达式”列中,单击省略号按钮“(…)”以打开“表达式生成器”对话框。 Ø 在“表达式生成器”对话框中,展开“变量”节点。 Ø 将变量用户::varFileName 拖到“表达式”框中。 Ø 单击“确定”关闭“表达式生成器”对话框。 Ø 再次单击“确定”关闭“属性表达式编辑器”对话框。
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有