问题引入
有如下格式的文件,需要转换成如图二的格式导入数据库里,这就是所谓的列传行的格式,是一种逆透视的操作。用 Excel 透视操作或者写个 VBA 宏程序是可以实现的,我们现在用 SSIS 的逆透视组件来实现下。
系统环境
- Windows 7
- SQL Server 2008 R2
- Business Intelligence Development Studio
步骤
首先废话不多说,新建 SSIS 项目
拖入 EXCEL 数据源,选择源文件。
拖入逆透视组件
打开逆透视编辑器,这里有几个说明的地方:
- 1.可用输入列里面的传递,如果勾上了,这一列将不进行逆透视,就是直接输出到目标文件或者数据库里。
- 2.可用输入列里左边的复选框勾上就是需要将这列进行逆透视的操作,勾选后将会出现在下面。
- 3.下面目标列里默认是空的,需要自己填,这一列的值将会是源文件 1 号到 31 号每一列里的金额值,所以目标列填的都一样,略坑的是需要一个一个填,我复制黏贴了 30 遍。
- 4.透视键值列名最好改个名称,就是输入列逆透视后放在那一列下面,这里填的就是列名,1 号到 31 号将会作为“几号”列下面的值。
其他默认,按确定。确认完了,发现有错误。提示:
数据流任务 [逆透视 [116]] 出错: UnPivot 元数据不正确。在 UnPivot 转换中,对于所有设置了 PivotKeyValue 且指向相同 DestinationColumn 的输入列,其元数据都必须与 DestinationColumn 完全匹配。
这是啥问题,折腾了一点时间,在逆透视组件的高级编辑器了怎么调都不好使。仔细看了下,会不会是数据类型的问题,果然,逆透视列的数据类型不是都一样的,所以就添加了一个数据转换组件。
在数据转换组件里把字符串类型都转成浮点型数据。
最好把之前的那个逆透视的组件删了,重新建一个。把逆透视的列改成类型转换的输出列,目标列又复制黏贴了 30 次。
这样逆透视的组件就不会报错了,然后执行下数据流,哟,报错了,提示一个或多个组件未能通过验证。
噢,才想起来项目属性里有一个调试设置什么 64 位运行环境的要改成 false 才可以。
这样就不会报错了。
拖入 EXCEL 目标组件,列与逆透视组件输出列对应,目标 EXCEL 是有表头的。
转换初见成果啊,但是看着有点不爽,想把年月跟几号列合成一个日期列,日期格式是 20160406 这种的,在目标表里加入一个日期列。
拖入一个派生列组件,在派生列编辑器里增加一列派生列,表达式如下,就是对字符串的截取拼接,这里就不解释了,然后在 Excel 目标里给这个派生列映射到目标表的“日期”。
(DT_WSTR,6)[年月] + RIGHT("0" + SUBSTRING([几号],1,FINDSTRING([几号],"日",1) - 1),2)
重新执行下数据流,嗯,达到目的。
总结一下
- 在使用处理数据列的组件之前最好加一个数据类型转换的组件,看看有哪些数据类型是不符合我们预期的。
- 项目属性里的调试 64 位运行时不知道啥情况,为 TRUE 时会报错,得设置为 FASLE。
- 逆透视组件设置相对简单,找好逆透视的列以及行数据要放在哪列里就可以了。
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于