问题描述:
之前做ETL大都是将文件作为数据源,还没有完整的做过导出数据到平面文件的项目,更何况Excel这种奇葩的数据源或者destination了。既然需求来了,那小哥也就只有迎头而上了。此次项目需要将monthly数据处理后导入Excel发给客户,每月文件名自然要用月份标识,所以也需要每月自动生成Excel文件,继而将数据导入其中。之前在网上查过的方法大都为在SSIS package中添加.net script,是用office dll或者COM组件在script里面写.net脚本生成excel。后来一想,虽然SSIS作为一种算不上主流的ETL工具,但微软的工具也不至于太out,应该ETL工具会有自带的生成Excel的办法。
环境:SSIS 2008
解决方法:
首先当然要创建Excel Connection Manager,虽然是每月动态生成Excel文件,但在开发的时候还是需要一个Excel template定义好column这些(和寻常ETL差不多不赘述了)。
用过SSIS的同学都应该对Execute SQL Task不陌生,以前通常用来在package里面调用procedure或者直接用direct input的方式写入要运行的SQL。但我平时很少去注意ConnectionType这个参数。将这个组件拖出的时候默认是OLEDB,但是在下拉列表里面我们可以发现一个神奇的东东,那就是EXCEL。选中之后再去下面的Connection里面选中自己建立的Excel connection manager,在SQLStatement里面用Direct Input或者其他方式创建自己要生成的Excel sheet结构(SSIS工具里面是将Excel sheet当做table的方式进行处理的, 也就是说在这里就要把sheet创建好),