如前文所述已经实现自动把DHCP数据导出到text文本,本文将介绍如何自动把text文本内容导入到SQL Server数据库。SQL Server里面有导入和导出数据工具,可以一次性将平面文件源text文本导入到数据库,也可以在导入设置中选择生成SSIS包。为了实现当执行导入数据时有新数据进行插入,以IP地址为依据,如果有数据更改则进行更新,我选择生成一个SSIS包并在SQL Server作业中调用定期执行的方式来实现。步骤如下:
1. 先运行导入和导出数据工具导入一次dhcp数据以生成相应的dhcp数据表。
2. 制作自动从dhcp数据文件导入数据的SSIS包,具体做法可参照 文章http://blog.csdn.net/zjcxc/archive/2006/09/10/1202876.aspx的思路来做,在本例中是以IP地址作为依据,具体如下:
(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,验证数据导入的正确性
3. 编辑好SSIS包后,点击文件->将xxx.dtsx的副本另存为将此SSIS包保存到dhcp数据所在的数据库服务器中,指定目录并命名SSIS包。
4. 登录到dhcp数据所在的SQL Server数据库服务器,打开SQL Server代理->作业,新建作业,新建步骤,在类型下选择”SQL Server Integration Services包”,选择在上步中所部署的SQL Server服务器,并选择刚才所部署的SSIS包。
5. 新建计划以执行此SSIS包,可以根据际环境定义执行时间,如每隔15分钟执行一次。
6. 最后确定后直接在此作业点击右键选择“作业开始步骤”来执行此步骤并确认是否成功。
通过以上步骤即成功实现自动导入text文本数据到SQL Server 数据库并实现及时更新,但是以上方法未实现删除功能,即新的dhcp数据中没有的数据,它无法在SQL Server数据库中也做相应的删除,目前本人还不会,待实现后再做相应的更新。