前面写过一篇博客: 用SQL作业+导入导出向导(SSIS包)实现灵活的数据传输
而实际上, 定期从SQL Server中导出EXCEL文件的同学更多, 这也是比较常见的需求。
下面以 Win10+SQL Server2017 为例,实现导出EXCEL文件。
一、随便在一个库上右键, 在菜单中找到 “导出数据” 子菜单。
二、数据源选择本机实例
三、选择目标为 EXCEL文件。
注:如果出现错误 “未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序”
四、选择编写查询:
五、贴语句, 本人这里的脚本如下(正好excel2007的最大行数,运行时间比较长):
如果做测试,建议写个最简单的 select 1 as r 就可以了。
--EXCEL 2003 最大 65536 行
--EXCEL 2007 最大 1048576 行
--下面的脚本生成数据 1048575 +标题 正好达到最大
;WITH cte AS (
SELECT sv.number
FROM [master].dbo.spt_values AS sv
WHERE sv.[type] = 'P'
AND sv.number BETWEEN 1 AND 1024
)
SELECT REPLICATE('0',7-LEN(LTRIM(t.rid)))+LTRIM(t.rid) as rid
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid
FROM cte AS a CROSS APPLY cte AS b
)AS t
WHERE t.rid<1048576
六、可以编辑SQL Server与EXCEL列头数据类型的映射:
七、不要立即运行,选择 “保存SSIS包” , 并且是 "SQL Server" 。
八、
九、
十、
十一、做好上面的包, 下面只需要在作业的步骤中如下处理就可以的了:
完成作业, 右键执行测试一下是否能正常完成:
查看历史记录, 本次导出需要 6 分 19 秒。
至于每天导出时, 如何生成不同的文件名, 可以增加一个新步骤, 由 powershell 脚本来完成。
另外, 如果出现异常:
Microsoft (R) SQL Server 执行包实用工具 Version 10.0.1600.22 for 64-bit 版权所有 (C) Microsoft Corp 1984-2005。保留所有权利。 开始时间: 17:37:37 错误: 2010-11-21 17:37:37.98 代码: 0xC00F9304 源: Package 连接管理器“Excel 连接管理器” 说明: SSIS 错误代码 DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: 因为没有可用的 OLE DB 访问接口,所以在 SSIS 的 64 位版本中不支持 Excel 连接管理器。 错误结束 错误: 2010-11-21 17:37:37.98 代码: 0xC020801C 源: 数据流任务 Excel 源 [176] 说明: SSIS 错误代码 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。对连接管理器“Excel 连接管理器”的 AcquireConnection 方法调用失败,错误代码为 0xC00F9304。可能在此之前已经发出错误消息,提供了有关 AcquireConnection 方法调用失败原因的详细信息。 错误结束 错误: 2010-11-21 17:37:37.98 代码: 0xC0047017 源: 数据流任务 SSIS.Pipeline 说明: 组件“Excel 源”(176) 未能通过验证,返回的错误代码为 0xC020801C。 错误结束 错误: 2010-11-21 17:37:37.98 代码: 0xC004700C 源: 数据流任务 SSIS.Pipeline 说明: 一个或多个组件未能通过验证。 错误结束 错误: 2010-11-21 17:37:37.98 代码: 0xC0024107 源: 数据流任务 说明: 任务验证期间出错。 错误结束 DTExec: 已返回包执行 DTSER_FAILURE (1)。 开始时间: 17:37:37 完成时间: 17:37:37 占用时间: 0.406 秒. 包执行失败。. 该步骤失败。
解决方法如下
1.右键作业-[属性]-[步骤]-选择步骤并点击[编辑]
2.在常规选项,选择[执行选项]-选中[使用32位运行时]即可
原因64位不支持Excel 连接管理器所以我们要调成32位运行.