用SQL作业+导入导出向导(SSIS包)实现定期导出EXCEL文件

前面写过一篇博客： 用SQL作业+导入导出向导(SSIS包)实现灵活的数据传输

而实际上， 定期从SQL Server中导出EXCEL文件的同学更多， 这也是比较常见的需求。

下面以 Win10+SQL Server2017 为例，实现导出EXCEL文件。

一、随便在一个库上右键， 在菜单中找到 “导出数据” 子菜单。

二、数据源选择本机实例

三、选择目标为 EXCEL文件。

注：如果出现错误 “未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序”

需要安装： http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

四、选择编写查询：

五、贴语句， 本人这里的脚本如下（正好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位运行.