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

 

使用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这种重量级的工具啊，所以我卷起袖子就大干一场，写了多种方式，但结果很特别。。。。。。