代码改变世界

数据处理利器-SSIS入门与进阶

2010-06-29 14:42 by 灵感之源, ... 阅读, ... 评论, 收藏, 编辑

 

使用SSIS,首先是因为工作需要,其次,在澳洲找工作的2个星期里,我发现招聘需求中有太多技术我没有掌握了,其实这些技术,只需要几天就可以入门,几个星期就可以进阶,为什么不花一点时间去掌握一门新的技术呢?多一门技术与少一门差异是很大的,招聘需求往往会写明必须掌握的技术,你缺一门,你就不好意思打招呼了。


SSIS是从MS SQL 2005开始引入的,实际上是DTS的马甲,是SQL Server Integration Service的缩写。DTS也好,SSIS也罢,都是ETL(Extract Transform Load)工具,一般用来导入数据到数据库。SSIS比普通的ETL更进一步,它是可视化的,用Visual Studio来开发,包文件(*.dtsx)采用的是XML格式。跟Office2007+系列文档扩展名一样,都是???x,代表xml格式,但他没有压缩成zip文件罢了。

 

我在新格式的工作内容是研究SSIS的性能。公司是做能源管理的,每隔一段时间(如15分钟)需要从测量表中提取数据,不断积累后,一个测量表5年的数据有231MB,134万条。目前每天要处理6500多个文件,现有活动数据是3亿条记录。

 

 


注意:因为移民到了澳洲悉尼,系统和开发环境全部是英文,我也不知道中文对应是什么,所以以下说明全部使用英文,并加上我猜想的中文。

 

首先使用SSIS创建一个简单的Integration Services Project: File->New->Project->Business Intellegence Projects->Integration Services Project

(新建项目1)

(新建项目2)

 

添加一个Foreach Loop Container

 

, 双击这个Foreach Loop Container,点击左边的Collection,选择来源文件目录(Folder),输入文件类型(Files)。

 

点击Variable Mappings,点击New Variable,在Name输入变量名称。这个变量是目录循环中每个文件的值,稍后会用在每个文件的处理中。

 

 

再在里面添加一个Data Flow Task

 

 

 

,双击这个Data Flow Task,进入了Data Flow标签(Control Flow右边)。因为原始文件是从测量表读取出来然后写到CSV文件的,所以我们使用Flat File Source(就是文本文件,CSV文件也属于这类)。

  

双击Flat File Source,Flat File connection manager,点右边的New新建一个文本文件连接,给连接一个名字,选择一个例子文件,按照你的情况输入header rows to skip(跳过头列),如果第一列是列名称,你应该输入1。

 

 

点击Advanced(高级),给每列选择正确的数据类型,确定。

 

点击Columns(列),给每列起个名字。

 

在我们的数据中,都是基本的设备、日期、温度、湿度、气压等等。但某些记录是错误的(譬如没有日期),这些记录就要忽略,日期是细分为:年、月、日、小时、分钟的,必须合并,还有温度、湿度某些情况是没有的,你就要处理为NULL。

 

对于错误的记录(日期为空),我们要添加一个Conditional Split,在Condition(条件)中输入 “LEN(TRIM(Year)) > 0”(只需要判断年是否为空)。

 

关于表达式,需要特别说明一下:你可以选作左边的变量或者列,或者右边的各种内置函数。你只需要直接拖放变量、列或者函数到输入框便可。

 

对于完整的日期,我们需要添加一个Derived Column(派生列),从Conditional Split拖放关系,要求你选择跳转到指定流程的条件。

 

双击该派生列,在Expression(表达式)中输入“Year + "-" + Month + "- " + Day + " " + Hour + ":" + Minute + ":00"”(构造标准的日期格式,如:2010-06-29 16:11),日期类型选database timestamp(根据你自己的实际情况吧)。

 

对于NULL数值类型,你也需要添加一个派生列,然后在表达式中输入LEN(TRIM(Temperature)) > 0 ? (DT_R8)Temperature : NULL(DT_R8)。这里用DT_R8是因为温度是double。

 

 

然后添加一个导入目标,我们的目的是把数据导入到MS SQL Server,所以添加一个SQL Server Destination,双击进入编辑

 

 

点击New新建一个数据库连接。

 

点击Mapping(映射),把CSV文件的列对应到数据库表

 

接下来就是运行了,如果你正确设置,数据自然会导入到数据库。

 

这里需要注意,SSIS的设计有颇多的bug,譬如下面提及的脚本编译问题,还有在设计器中,不能通过编辑目标数据来直接编辑连接,你要双击下方的控件列表。另外,当你编辑连接,你会发现之前选择的例子文件会丢失,每次你都必须重新选择。

 

如果要发布到SQL Server,你要修改SSIS的项目属性:Project->(your project name) Properties->Deployment Utility->CreateDeploymentUtility->True。然后双击bin\Deployment下面的Deployment文件,按照提示一步步Next便可(建议安装到MS SQL Server,而不是文件系统)。如果要查看已经安装的,打开SQl Server Management Studio,选择Integration Services,打开。


在开发SSIS的过程中,你可能会遇到一些诡异的情况。譬如:
1.类型转换,你在Prorgress(进度)标签中可以查看详细的结果,但往往结果都比较含糊。一般都是类型没有选对,譬如SSIS会自动选回字符串类型,你又要手工改回database timestamp
2.目标数据库中的数据表字段变化了,或者来源文件格式变了,你运行,肯定错误,但在Progress(进度)中,你难以看出个门道。一般看见设计图中的出现警告或者错误小图标,你鼠标挪上去停一下,就会给个模糊的提示,双击,会提示你修正。

 

一些特别情况:
1.内置的控件无法满足需要,怎么办?自己写控件:陈希章同学写的为SSIS编写自定义任务项(Task)之入门篇 。这里还有另外一个例子:执行SQL Job并等待其结束

2.需要复杂的处理:在Control Flow中用Script Task,在Data Flow中用Script Component;

关于脚本:添加一个Script Task,双击,点击Script(脚本),如果你是SQL 2005 SP1或以下,你就必须选PrecompileScriptIntoBinaryCode,否则你将无法正确运行。或者你给2005安装SP2;

3.你需要让SSIS执行并尽量等待你去做其他事情(矛盾啊。。。),你可以添加一个Script Task,输入System.Threading.Thread.Sleep(想多长就多长);

 

关于变量:你需要注意变量的作用域。一般的作用域是Package(包)。在脚本中,如果你想访问变量,你就必须在ReadOnlyVariables(只读变量)和ReadWriteVariables(可读写变量)中输入变量名称(多个变量用逗号分隔)。然后在编辑脚本代码中,使用Dts.Variables("YourVariableName")来访问变量。

 

关于邮件发送,如果使用内置的Send Mail Task,你会发现无法指定登陆用户名和密码。我改用脚本调用sourceforge上opensmtp,或者,你可以使用MS SQL Server自带的邮件功能,然后用SQL Excute Task执行,譬如:

exec msdb.dbo.sp_send_dbmail @profile_name = 'Foo',
    @recipients = 'FooReceiver',
    @subject = 'FooSubject',
    @body = 'FooBody'
    @body_format = 'Text'

 

稍后我会给出使用最老套的c#代码导入数据方式(包括多线程方式等多种方式),和SSIS比较性能,因为我觉得写c#代码怎么也不能输给SSIS这种重量级的工具啊,所以我卷起袖子就大干一场,写了多种方式,但结果很特别。。。。。。