PowerShell 2.0 实践（十五）添加任务计划并将Excel导入SQL Server
近期在负责一个小项目，每月定期将一些Excel报表中的数据导入到SQL Server中，方便公司系统与第三方系统的交互。
中间服务器将我方客户的服务器和第三方公司的服务器衔接起来，我的任务就是将我方客户的Excel报表数据导入到中间服务器上的SQL Server 2000中。
我的处理思路是这样的，由于中间服务器的操作系统是Windows Server 2003，故需要创建一个计划任务，这个计划任务每月定期执行一个PowerShell脚本，执行数据导入等相关操作。
本系列所有测试脚本均在Windows Server 2008 R2 DataCenter (PowerShell 2.0) + PowerGUI Script Editor Free Edition x64中测试通过。
注：本次在VMWare Workstation 7.1.3(Windows Server 2003 R2 x86)中测试通过。
转载请注明出处：http://www.cnblogs.com/brooks-dotnet/archive/2010/12/15/1907199.html
1、准备工作
为了模拟中间服务器的环境，我准备了一个VMWare的虚拟机，操作系统是Windows Server 2003 R2 x86：
2、创建任务计划
Windows Server 2003 中有个任务计划功能，可以在设定的时间段执行任务，非常灵活，可以自定义执行的时间、次数、间隔等。
可以看到，搜狗拼音已经创建了一个任务计划：
使用GUI创建任务计划是比较简单的，但是对于项目部署来说就比较麻烦了，最好能自动化部署，所以考虑使用脚本来操作。
Windows提供了一个命令行工具：Schtasks.exe，可以在DOS下对任务计划进行创建、修改、删除等高级操作。可以在这里查看其详细说明。
我创建的任务计划批处理脚本如下：
@echo off
REM 创建任务计划
schtasks /create /tn import /tr "powershell -File \"C:\Excel to SQL Server Demo.ps1\"" /sc monthly /mo 1 /d 15 /st 22:00 /ru ""
echo 创建计划任务成功！
Pause
其中核心的一句命令为：powershell -File "C:\Excel to SQL Server Demo.ps1"，注意由于双引号出现了嵌套，故需要用\进行转义。
创建的任务计划是：在每月的15号晚上10点钟，以System账户执行脚本C:\Excel to SQL Server Demo.ps1
任务名为：import。Excel to SQL Server Demo.ps1为另一个PowerShell脚本，负责导入数据的。
执行该批处理，运行结果如下：
在控制面板里的任务计划里也可以看到创建的任务计划：
3、准备工作
任务计划创建好后，下面我们就要编写将Excel数据导入到SQL Server中的PowerShell脚本了。
安装SQL Server 2008 或 R2后会安装两个PowerShell扩展：SqlServerCmdletSnapin100、SqlServerProviderSnapin100，提供了本地及远程执行SQL语句的能力。
在这一次的测试环境中，中间服务器安装的是SQL Server 2000，显然不能随便将其升级，故需要手动来安装这两个PowerShell扩展。那么该怎样手动安装呢？
我在国外的一篇博客中找到了解决方案：
3.1、安装.NET Framework 3.5 With SP1，下载地址
3.2、安装Office 2010 PIA，下载地址，下载地址
注：我在虚拟机中安装Office 2010 PIA时出了些问题，始终安装不上，故暂时安装Office 2010 x86进行测试（Office 2010包含PIA）。
3.3、安装Windows 管理框架(包含PowerShell 2.0)，下载地址
3.4、安装Microsoft® SQL Server® 2008 R2 功能包，下载地址
功能包中的组件有很多，只需要安装如下几个，安装也按照下面列出的顺序(这里仅给出 x86架构的下载地址，如果你需要其他架构，请到微软网站下载)：
Microsoft® System CLR Types for SQL Server® 2008 R2，下载地址
Microsoft® SQL Server® 2008 R2 Native Client，下载地址
Microsoft® Core XML Services (MSXML) 6.0 SP 1，下载地址
Microsoft® SQL Server® 2008 R2 命令行实用工具，下载地址
Microsoft® SQL Server® 2008 R2 共享管理对象，下载地址
Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2，下载地址
3.5、更改PowerShell的执行策略
为了安全起见，PowerShell的默认执行策略为不允许执行任何脚本，详情参见：get-help about_signing 命令。
所以我们要将执行策略更改为：允许运行未签名脚本和来来自其他用户的签名脚本。
Set-ExecutionPolicy Remotesigned -Force
3.6、手动安装 SQL Server 2008 R2的PowerShell扩展
Set-Alias installutil $env:windir\microsoft.net\framework\v2.0.50727\installutil
installutil -i "C:\Microsoft.SqlServer.Management.PSProvider.dll"
installutil -i "C:\Microsoft.SqlServer.Management.PSSnapins.dll"
使用Set-Alias命令为installutil创建一个别名，其中installutil.exe是.NET框架自带的安装辅助工具，这里将注册SQL Server 的两个程序集。
这两个程序集的位置位于：C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn （x64）
C:\Program Files\Microsoft SQL Server\100\Tools\Binn（x86）
可以在SQL Server 2008 或 SQL Server 2008 R2的安装目录中找到：
当然，中间服务器是不一定安装有SQL Server 2008的，所以要从别的机器上复制出来。
使用Get-PSSnapin –Registered命令查看程序集是否安装成功：
3.7添加PSSnapin
注意PSSnapin不能重复添加
3.8、安装Microsoft Access Database Engine 2010 Redistributable
MADE 2010包含一系列的组件，使得非 Microsoft Office 应用程序可以使用这些组件从 Microsoft Office 2010 文件中读取数据，当然也是向后兼容的。
我准备在SQL语句中用OPENDATASOURCE函数读取Excel，故需要下载MADE 2010，下载地址。
3.9、在中间服务器上创建数据库Middle
Invoke-Sqlcmd -Query "IF NOT EXISTS(SELECT * FROM dbo.sysdatabases WHERE name = 'Middle')
BEGIN
CREATE DATABASE [Middle] ON PRIMARY
( NAME = N'Middle', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Middle.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Middle_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Middle_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO" -ServerInstance "." -Username sa -Password ******
3.10、在Middle数据库中创建表Users
这里假定向中间服务器中的数据库Middle的Users表中导入数据，创建表的脚本如下：
Invoke-Sqlcmd -Query "
USE Middle
GO
IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE dbo.Users
(
UserID INT IDENTITY(1, 1) NOT NULL,
UserName VARCHAR(20) NULL,
UserPass VARCHAR(20) NULL,
CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID)
)
END
GO" -ServerInstance "." -Database Middle -Username sa -Password ******
我准备了5个Excel文件做测试，简单起见，仅包含一个工作表，两列数据，第一列作为UserName，第二列作为UserPass：
3.12、创建存储过程
因为OPENDATASOURCE函数不支持直接传递参数，故需要使用动态SQL语句。
Invoke-Sqlcmd -Query "
USE Middle
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_导入Users表数据') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.sp_导入Users表数据
END
GO
CREATE PROCEDURE dbo.sp_导入Users表数据
(
@ACEVersion VARCHAR(50),
@ExcelPath NVARCHAR(1000),
@ExcelVersion VARCHAR(50),
@SheetName VARCHAR(50)
)
AS
DECLARE @SQL VARCHAR(8000)
SET @SQL = '
INSERT INTO Users(UserName, UserPass)
SELECT * FROM OPENDATASOURCE(''' + @ACEVersion + ''', ''Data Source = ' + @ExcelPath + ';Extended Properties = ' + @ExcelVersion + ''')...[' + @SheetName + '$]'
EXECUTE(@SQL)
GO" -ServerInstance "." -Database Middle -Username sa -Password ******
4、将Excel数据导入SQL Server
调用存储过程
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$app = New-Object -TypeName Microsoft.Office.Interop.Excel.ApplicationClass
$dir = New-Object -TypeName System.IO.DirectoryInfo -ArgumentList C:\tmp
foreach($file in $dir.GetFiles("*.xlsx", [System.IO.SearchOption]::AllDirectories))
{
$ExcelPath = $file.FullName
Invoke-Sqlcmd -Query "EXECUTE dbo.sp_导入Users表数据 'Microsoft.ACE.OLEDB.12.0', '$ExcelPath', 'Excel 12.0', 'User'" -Database Middle -Username sa -Password ******
}
$app.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($app)
$app = $null
这里有点奇怪，我安装的是Microsoft.ACE.OLEDB.14.0，但是却只能使用Microsoft.ACE.OLEDB.12.0，使用14.0会提示未注册：
关于连接字符串的写法参见ConnectionString网站。
最终运行结果：
小结：
将Excel导入到SQL Server中有很多种解决方案，具体应取决于业务。我这里采用纯脚本的方式来处理，优点是非常灵活，速度较快，可扩展性很强。且PowerShell建立在.NET之上，可以调用其他类库，进一步扩展了其适用范围。如调用存储过程时使用了Office 2010 PIA、System.IO等，和一般的C#代码很相像。缺点是需要安装很多依赖组件，如.NET 3.5 SP1，SQL Server 2008 R2 功能包等，部署准备工作复杂，且难以对待导入的Excel进行更细粒度的控制，不能精确到单元格。若Excel中的数据格式很不规整、数据类型混乱，则会带来一些麻烦，实际项目中应根据需求决定使用哪种方案，合适的才是最好的。
