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

问题描述:

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

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

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

  1. 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   阿联酋

  1. 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。
  • 当系统提示重命名包对象时,单击“是”。
  1. 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。
  • 单击“确定”。
  1. 4.          SSIS包添加数据流任务
  • 单击“控制流”选项卡。
  • 在“工具箱”中,展开“控制流项”,并将一个数据流任务拖到“控制流”选项卡的设计图面上。
  • 在“控制流”设计图面中,右键单击新添加的数据流任务,再单击“重命名”,将名称更改为Import Data。
  1. 5.          在数据流任务中设置数据流源
  • 打开“数据流”设计器,方法是双击Import Data 数据流任务或单击“数据流”选项卡。
  • 在“工具箱”中,展开“数据流源”,然后将“平面文件源”拖动到“数据流”选项卡的设计图面上。
  • 在“数据流”设计图面上,右键单击新添加的“平面文件源”,单击“重命名”,然后将该名称更改为Source Data。
  • 双击此平面文件源,打开“平面文件源编辑器”对话框。
  • 在“平面文件连接管理器”框中,键入或选择Source。
  • 单击“列”并验证列名是否正确。
  • 单击“确定”。
  1. 6.          在数据流任务中添加查找处理组件
  • 在“工具箱”中,展开“数据流转换”,然后将“查找”拖动到“数据流”选项卡的设计图面上。将“查找”直接放置在Source Data 源的下面。
  • 单击Source Data 平面文件源,并将绿色箭头拖动到新添加的“查找”转换中,以连接这两个组件。
  • 在“数据流”设计图面上,右键单击新添加的“查找”转换,单击“重命名”,然后将该名称更改为Lookup id。
  • 双击Lookup id 转换。
  • 在“查找转换编辑器”对话框的“OLE DB 连接管理器”框中,确保显示localhost.tempdb。
  • 在“使用表或视图”框中,键入或选择[dbo].[tb]。
  • 单击“列”选项卡。
  • 在“可用输入列”面板中,将id 拖放到“可用查找列”面板的id 上。
  • 单击“确定”。
  1. 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 输入列是否已正确映射到目标列。如果映射了同名列,则说明映射正确。
  • 单击“确定”。
  1. 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个?,这是固定的。
  • 单击“确定”。
  1. 9.          测试
  • 按“F5”执行SSIS包
  • 执行结束(所有的组件都变为绿色),你会看到数据流向“Inset Data”的有两条数据
  • 双击“连接管理器”中的Source,重新设置文件名为D:/test/d2.txt。
  • 单击“确定”
  • 按“Ctrl+Shift+F5”,重新启动SSIS
  • 执行结束(所有的组件都变为绿色),你会看到数据流向“Inset Data”的有两条数据,流向“Update Data”的有1条数据
  • 最后,在数据库中查询tempdb.dbo.tb,验证数据导入的正确性
  1. 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 拖到“表达式”框中。
  • 单击“确定”关闭“表达式生成器”对话框。
  • 再次单击“确定”关闭“属性表达式编辑器”对话框。

转自: http://blog.csdn.net/zjcxc/article/details/1202876

时间: 10-19

(转)SSIS处理导入数据时, 存在的更新, 不存在的插入的相关文章

SQL Server导入数据时“启用标示插入”详解

在SQL Server中导入数据时,会有一个"启用标示插入"的选项,突然间懵逼了,这到底啥意思?我选与不选这个选项,结果好像没区别!不科学啊这,"存在即合理",我相信这个选项肯定有它的用处,只是我不知道罢了. 于是我去查资料,在CSDN论坛里找到了可能的解释: 1)首先:"启用标示插入"只对标示列(identity定义的列)起作用.所以如果你要使用这个功能,肯定得有标示列了.我当时狂晕的地方在于,我没在数据库创建表,而是直接在导入数据的过程中创建

DB2导入数据时乱码问题

1.由于导入import导入数据时乱码,一直找不到解决办法,于是就用load导入 LOAD后,发现某些表检查挂起( 原因码为 "1",所以不允许操作 SQLSTATE=57016 ) 解决办法:set integrity for xxxxxx immediate checked 2.表锁定(原因码为7) 解决办法:reorg table xxxx 3.去重 select distinct a.column from table1 as a inner join table2 as b

MYSQL导入数据时,出现错误:Incorrect string value: &#39;\xF0\x9F...&#39; for column &#39;XXX&#39; at row 1

Incorrect string value: '\xF0\x9F...' for column 'XXX' at row 1 这个问题,原因是UTF-8编码有可能是两个.三个.四个字节.Emoji表情或者某些特殊字符是4个字节,而MySQL的utf8编码最多3个字节,所以数据插不进去. 我的解决方案是这样的 1.在mysql的安装目录下找到my.ini,作如下修改: [mysqld] character-set-server=utf8mb4 [mysql] default-character-

Navicat导入数据时发生了报错 --- 1153 - Got a packet bigger than &#39;max_allowed的处理办法

今天我在使用Navicat导入.sql文件数据时,发现本来是80万条的数据,结果只导入了10万条左右,而且在其错误信息日志中,我发现了这样一条错误:1153 - Got a packet bigger than 'max_allowed. 度娘了一番后,发现越来是我的SQL文件远远大于MySQL默认执行的文件大小,所以执行不过去. 解决方法: 在MySQL安装目录下找到文件my.ini,搜索[mysqld],在其下面添加一句话 max_allowed_packet=521M  ##该处按照自己的

Sqlserver 平面文件导入/ SSIS FlatFileSource导入文件时 出现LocaleID is not installed报错问题

最近在使用SqlServer和SSIS导入一个CSV文件到数据库时(SSIS选用的FlatFileSource作为数据流源),老是遇到  The LocaleID 4 is not installed on this system. 这个错误. 后来搞了老半天发现是FlatFileSource的Locale选项选择上出现了问题,由于要导入的CSV文件中包含中文,所以在用Sqlserver/SSIS导入csv时我在FlatFileSource的Locale上选择了Chinese (Simplifi

asp.net线程批量导入数据时通过ajax获取执行状态

最近因为工作中遇到一个需求,需要做了一个批量导入功能,但长时间运行没个反馈状态,很容易让人看了心急,产生各种臆想!为了解决心里障碍,写了这么个功能. 通过线程执行导入,并把正在执行的状态存入session,既共享执行状态,通过ajax调用session里的执行状态,从而实现反馈导入状态的功能! 上代码: 前端页面 <!DOCTYPE html> <html lang="en"> <head>  <meta charset="UTF-8

解决导入数据时提示分区数据不能导入问题

oracle导入数据的时候,有时会出现分区数据不能导入的问题,以及在迁移表空间的时候可能存在的索引状态为'UNUSABLE'的隐患,下面为解决方案: --查询出所有的状态为UNUSABLE的索引,并生成出来SQL select   'alter index ' ||  index_name || ' rebuild ;' from user_indexes where status='UNUSABLE';--例子: alter index PK_UN_ORGANIZATION_FUNCTION

Oracle 用中文作为关键字查询无数据及sql loader 导入数据时数据分隔异常的解决办法

前提:电脑A使用sqlldr向电脑B中的Oracle批量导入数据.数据分隔及查询都正常. 拷贝A中的源数据及ctl文件到电脑C中,拷贝B中的Oracle安装文件并安装至电脑C中. 电脑C使用ctl将源文件导入本机Oracle中之后发现.导入的数据分隔错误,比如[email protected]@ccc格式的数据,sqlldr导入的时候用@分隔.但是实际导入结果并不是column1=aaa,column2=bbb, column3=ccc:有部分数据变成了[email protected],[em

mysql通过sql文件导入数据时出现乱码的解决办法

首先在新建数据库时一定要注意生成原数据库相同的编码形式,如果已经生成可以用phpmyadmin等工具再整理一次,防止数据库编码和表的编码不统一造成乱码. 方法一: 通过增加参数 –default-character-set = utf8 解决乱码问题 mysql -u root -p password < path_to_import_file –default-character-set = utf8 方法二: 在命令行导入乱码解决 1. use database_name; 2. set n