SSIS 进行列转行格式的转换

本贴最后更新于 3133 天前,其中的信息可能已经东海扬尘

问题引入

有如下格式的文件,需要转换成如图二的格式导入数据库里,这就是所谓的列传行的格式,是一种逆透视的操作。用 Excel 透视操作或者写个 VBA 宏程序是可以实现的,我们现在用 SSIS 的逆透视组件来实现下。

行格式

列格式

系统环境

  • Windows 7
  • SQL Server 2008 R2
  • Business Intelligence Development Studio

步骤

首先废话不多说,新建 SSIS 项目

新建 SSIS 项目

拖入 EXCEL 数据源,选择源文件。

拖入 EXCEL 数据源

拖入逆透视组件

逆透视组件

逆透视编辑器

打开逆透视编辑器,这里有几个说明的地方:

  • 1.可用输入列里面的传递,如果勾上了,这一列将不进行逆透视,就是直接输出到目标文件或者数据库里。
  • 2.可用输入列里左边的复选框勾上就是需要将这列进行逆透视的操作,勾选后将会出现在下面。
  • 3.下面目标列里默认是空的,需要自己填,这一列的值将会是源文件 1 号到 31 号每一列里的金额值,所以目标列填的都一样,略坑的是需要一个一个填,我复制黏贴了 30 遍。
  • 4.透视键值列名最好改个名称,就是输入列逆透视后放在那一列下面,这里填的就是列名,1 号到 31 号将会作为“几号”列下面的值。

其他默认,按确定。确认完了,发现有错误。提示:

数据流任务 [逆透视 [116]] 出错: UnPivot 元数据不正确。在 UnPivot 转换中,对于所有设置了 PivotKeyValue 且指向相同 DestinationColumn 的输入列,其元数据都必须与 DestinationColumn 完全匹配。

报错

这是啥问题,折腾了一点时间,在逆透视组件的高级编辑器了怎么调都不好使。仔细看了下,会不会是数据类型的问题,果然,逆透视列的数据类型不是都一样的,所以就添加了一个数据转换组件。

数据类型

数据类型

数据类型转换

在数据转换组件里把字符串类型都转成浮点型数据。

逆透视编辑器

最好把之前的那个逆透视的组件删了,重新建一个。把逆透视的列改成类型转换的输出列,目标列又复制黏贴了 30 次。

这样逆透视的组件就不会报错了,然后执行下数据流,哟,报错了,提示一个或多个组件未能通过验证。

再次报错

噢,才想起来项目属性里有一个调试设置什么 64 位运行环境的要改成 false 才可以。

项目属性

这样就不会报错了。

顺利执行

拖入 EXCEL 目标组件,列与逆透视组件输出列对应,目标 EXCEL 是有表头的。

EXCEL 目标组件

转换初见成果啊,但是看着有点不爽,想把年月跟几号列合成一个日期列,日期格式是 20160406 这种的,在目标表里加入一个日期列。

写入 Excel 文件

拖入一个派生列组件,在派生列编辑器里增加一列派生列,表达式如下,就是对字符串的截取拼接,这里就不解释了,然后在 Excel 目标里给这个派生列映射到目标表的“日期”。

(DT_WSTR,6)[年月] + RIGHT("0" + SUBSTRING([几号],1,FINDSTRING([几号],"日",1) - 1),2)

派生列

重新执行下数据流,嗯,达到目的。

执行成功

总结一下

  • 在使用处理数据列的组件之前最好加一个数据类型转换的组件,看看有哪些数据类型是不符合我们预期的。
  • 项目属性里的调试 64 位运行时不知道啥情况,为 TRUE 时会报错,得设置为 FASLE。
  • 逆透视组件设置相对简单,找好逆透视的列以及行数据要放在哪列里就可以了。
  • BI
    5 引用 • 6 回帖
  • SSIS
    2 引用 • 3 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...