sql-server - 如何从部署在64位服务器上的SSIS包访问Excel数据源？

Question

我有一个SSIS包，可以将数据导出到几个Excel文件中以传输给第三方。为了使它能够在64位服务器上作为计划的作业运行，我了解我需要将该步骤设置为CmdExec类型，并调用DTExec的32位版本。但是我似乎无法正确地传递命令来传递Excel文件的连接字符串。

到目前为止，我有这个：

DTExec.exe /SQL \PackageName /SERVER OUR2005SQLSERVER /CONNECTION 
LETTER_Excel_File;\""Provider=Microsoft.Jet.OLEDB.4.0";"Data 
Source=""C:\Temp\BaseFiles\LETTER.xls";"Extended Properties=
""Excel 8.0;HDR=Yes"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

这给了我错误： Option "Properties=Excel 8.0;HDR=Yes" is not valid.

我已经尝试了一些带引号的变体，但还无法正确完成。

有谁知道如何解决这一问题？

UPDATE:

感谢您的帮助，但由于它们似乎仅适用于64位版本，因此我现在决定使用CSV文件。

Answer 1

此分步示例适用于可能会偶然发现此问题的其他人。本示例使用SSIS 2005并使用SQL Server 2005 64位版本服务器运行作业。

这里的答案仅集中于解决问题中提到的错误消息。该示例将演示重新创建问题的步骤以及引起问题的原因，然后介绍如何解决此问题。

NOTE:我建议使用选项将软件包配置值存储在数据库中，或者在环境变量的帮助下使用间接XML配置。此外，创建Excel文件的步骤将使用模板完成，然后将其移动到其他文件夹中进行存档。这些步骤不在本文中讨论。如前所述，本文的目的是解决错误。

让我们继续该示例。我也在博客中找到了有关此答案的信息，该答案可在this link中找到。答案是一样的。

创建一个SSIS包（Steps to create an SSIS package）。本示例使用BIDS2005。我在开始时就以YYYYMMDD_hhmm的格式命名了该包，其后是SO代表堆栈溢出，然后是SO问题ID，最后是描述。我并不是说您应该这样命名您的包裹。这是我以后可以轻松参考的内容。请注意，我还有一个名为Adventure Works的数据源。我将使用Adventure Works数据源，该数据源指向从this link下载的AdventureWorks数据库。该示例使用SQL Server 2008 R2数据库。请参阅截图1。

在AdventureWorks数据库中，使用以下给定脚本创建一个名为dbo.GetCurrency的存储过程。

CREATE PROCEDURE [dbo].[GetCurrency]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
    TOP 10      CurrencyCode
            ,   Name
            ,   ModifiedDate 
    FROM        Sales.Currency
    ORDER BY    CurrencyCode
END
GO

在程序包的“连接管理器”部分，右键单击并选择“从数据源新建连接”。在“选择数据源”对话框上，选择“ Adventure Works”，然后单击“确定”。现在，您应该在“连接管理器”部分下看到Adventure Works数据源。

在包的“连接管理器”部分，再次右键单击，但是这次选择“新建连接…”。这是为了创建Excel连接。在“添加SSIS连接管理器”上，选择“ EXCEL”。在Excel连接管理器上，输入路径C：\ Temp \ Template.xls。将其部署到服务器时，将更改此路径。我选择了Excel版本Microsoft Excel 97-2005，并选择了保留复选框第一行的列名称已选中，以便在创建Excel文件时创建列标题。单击确定。将Excel连接重命名为Excel，只是为了保持简单。请参阅屏幕截图2-7。

在包上，创建以下变量。请参阅屏幕截图8。


SQLGetData：此变量的类型为String。这将包含存储过程执行语句。本示例使用值EXEC dbo.GetCurrency


屏幕快照＃9显示了存储过程执行语句EXEC dbo.GetCurrency的输出。

在程序包的“控制流”选项卡上，放置一个 Data Flow task并将其命名为“导出到Excel”。请参阅屏幕截图10。

双击“数据流任务”以切换到“数据流”选项卡。

在“数据流”选项卡上，放置一个 OLE DB Source以连接到SQL Server数据，以从存储过程中获取数据并将其命名为SQL。双击OLE DB源，以打开OLE DB源编辑器。在“连接管理器”部分上，从OLE DB连接管理器中选择Adventure Works，从“数据访问”模式的变量中选择SQL命令，然后从“变量名”下拉列表中选择变量User :: SQLGetData。在“列”部分，确保正确映射了列名称。单击“确定”关闭OLE DB源编辑器。请参阅屏幕截图＃11和＃12。

在“数据流”选项卡上，放置一个 Excel Destination以将数据插入Excel文件并将其命名为Excel。双击Excel目标以打开Excel目标编辑器。在“连接管理器”部分上，从OLE DB连接管理器中选择Excel，然后选择“表”或“数据访问”模式下的视图。此时，我们没有Excel，因为在创建Excel连接管理器时，我们仅指定了路径，但从未创建文件。因此，Excel工作表的下拉名称中将没有任何值。因此，单击“新建…”按钮（第二个“新建”按钮）以创建新的Excel工作表。在“创建表”窗口上，BIDS根据传入的数据源自动提供一个创建表。您可以根据自己的喜好更改值。我将通过保留默认值来简单地单击“确定”。工作表的名称将填充在Excel工作表的下拉名称中。工作表的名称取自任务名称，在本例中为Excel Destination，我们将其命名为Excel。在“映射”部分，确保正确映射了列名称。单击“确定”关闭Excel Destination Editor。请参阅屏幕截图＃13-＃16。

数据流任务配置完成后，其外观应如屏幕截图17所示。

通过按F5执行包。屏幕截图＃18-＃21显示了“控制流”和“数据流任务”中包的成功执行。此外，该文件是在Excel连接中提供的路径C：\ Temp \ Template.xls中生成的，并且存储过程执行输出中显示的数据与写入该文件的数据匹配。

该程序包是在本地计算机上的文件夹路径C：\ Learn \ Learn.VS2005 \ Learn.SSIS中开发的。现在，我们需要将文件部署到承载SQL Server 64位版本的服务器上以计划作业。因此，服务器上的文件夹为D：\ SSIS \ Practice。从本地计算机复制软件包文件（.dtsx）并将其粘贴到服务器文件夹中。另外，为了使程序包正确运行，我们需要在服务器上显示Excel电子表格。否则，验证将失败。通常，我创建一个Template文件夹，其中将包含与输出匹配的空Excel电子表格文件。稍后，在运行时，我将使用程序包配置将Excel输出路径更改为其他位置。对于此示例，我将使其保持简单。因此，我们将在本地计算机中生成的Excel文件复制到路径C：\ Temp \ Template.xls到服务器位置D：\ SSIS \ Practice。我希望SQL作业生成名称为Currencies.xls的文件。因此，将文件Template.xls重命名为Currencies.xls。请参阅屏幕快照＃22。

为了表明我确实要在64位版本的SQL Server上的服务器上运行作业，我在SQL Server上执行了命令SELECT @@ version，截图23显示了结果。

我们将使用执行包实用程序（dtexec.exe）生成命令行参数。登录到将在SQL作业中运行SSIS包的服务器。双击包文件，这将显示“执行包实用程序”。在“常规”部分，从“包源”中选择“文件系统”。单击省略号，然后浏览到程序包路径。在“连接管理器”部分，选择“ Excel”并将Excel文件中的路径从C：\ Temp \ Template.xls更改为D：\ SSIS \ Practice \ Currencies.xls。在实用程序中所做的更改将在“命令行”部分相应地生成一个命令行。在“命令行”部分，复制包含所有必需参数的命令行。我们不会从这里执行该程序包。单击关闭。请参阅屏幕截图＃24-＃26。

接下来，我们需要设置一个作业来运行SSIS包。我们无法选择SQL Server Integration Services包类型，因为它将在64位下运行，并且找不到Excel连接提供程序。因此，我们必须将其作为 Operating System (CmdExec)作业类型运行。转到SQL Server Management Studio并连接到数据库引擎。展开“ SQL Server代理”，然后右键单击“作业”节点。选择新作业…。在“作业属性”窗口的“常规”部分，提供作业名称为01_SSIS_Export_To_Excel，所有者将是创建作业的用户。我有一个名为SSIS的类别，因此将选择该类别，但默认类别为[未分类（本地）]，并提供简短说明。在“步骤”部分，单击“新建...”按钮。这将带来“作业步骤”属性。在“作业步骤”属性的“常规”部分上，将步骤名称提供为“导出到Excel”，选择类型 Operating system (CmdExec)，将默认的“运行方式”帐户保留为“ SQL Server代理服务帐户”，并提供以下命令。单击确定。在“新建作业”窗口上，单击“确定”。请参阅屏幕截图＃27-＃31。

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /FILE 
"D:\SSIS\Practice\20110723_1015_SO_21448_Excel_64_bit_Error.dtsx" 
/CONNECTION Excel;"\"Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source=D:\SSIS\Practice\Currencies.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";\""  
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI

新作业应显示在“ SQL Server代理–>作业”节点下。右键单击新创建的作业01_SSIS_Export_To_Excel，然后选择“在步骤…处启动作业”，这将开始执行作业。作业将按预期失败，因为这是此问题的背景。单击关闭以关闭“启动作业”对话框。请参阅屏幕截图＃32和＃33。

让我们看看发生了什么。转到“ SQL Server代理和作业”节点。右键单击作业01_SSIS_Export_To_Excel，然后选择查看历史记录。这将打开“日志文件查看器”窗口。您会注意到作业失败。展开红叉附近的节点，然后单击“步骤ID”值为1的行。在底部，您会看到错误消息 Option “8.0;HDR=YES’;” is not valid.单击“关闭”以关闭“日志文件查看器”窗口。请参阅＃34和＃35屏幕截图。

现在，右键单击作业，然后选择“属性”以打开“作业属性”。您也可以双击作业以打开“作业属性”窗口。单击左侧的步骤。然后单击编辑。用以下命令替换该命令，然后单击“确定”。在作业属性上单击确定以关闭窗口。右键单击作业01_SSIS_Export_To_Excel，然后选择“在步骤...启动作业”，这将开始执行作业。作业将无法成功执行。单击关闭以关闭“启动作业”对话框。让我们来看看历史。右键单击作业01_SSIS_Export_To_Excel，然后选择查看历史记录。这将打开“日志文件查看器”窗口。您会注意到该作业在第二次运行中成功完成。展开绿色勾号交叉点附近的节点，然后单击“步骤ID”值为1的行。在底部，您会看到消息“选项步骤已成功”。单击“关闭”关闭“日志文件查看器”窗口。文件D：\ SSIS \ Practice \ Currencies.xls将成功填充数据。如果您多次成功执行作业，则数据将被追加到文件中，并且您将找到更多数据。如前所述，这不是生成文件的正确方法。创建此示例的目的是为了解决此问题。请参阅屏幕截图＃36-＃38。

屏幕截图＃39显示了工作命令行参数和非工作命令行参数之间的差异。右边的是工作命令行，左边的是不正确的命令行。它需要使用反斜杠转义序列的另一个双引号来修复该错误。可能还有其他方法可以很好地解决此问题，但此选项似乎可行。

因此，该示例演示了一种从部署在64位服务器上的SSIS包访问Excel数据源时解决命令行参数问题的方法。

希望能帮助到某人。

屏幕截图：

＃1：Solution_Explorer



＃2：New_Connection_Data_Source



＃3：Select_Data_Source



＃4：New_Connection



＃5：Add_SSIS_Connection_Manager



＃6：Excel_Connection_Manager



＃7：Connection_Managers



＃8：变量



＃9：Stored_Procedure_Output



＃10：Control_Flow



＃11：OLE_DB_Source_Connections_Manager



＃12：OLE_DB_Source_Columns



＃13：Excel_Destination_Editor_New



＃14：Excel_Destination_Create_Table



＃15：Excel_Destination_Edito



＃16：Excel_Destination_Mappings



＃17：Data_Flow



＃18：Successful_Package_Execution_Control



＃19：成功_打包_执行_数据_流



＃20：C_Temp_File_Created



＃21：Data_Populated



＃22：File_On_Server



＃23：SQL_Server_Version



＃24：Execute_Package_Utility_General



＃25：Execute_Package_Utility_Connection_Managers



＃26：Execute_Package_Utility_Command_Line



＃27：Job_New_Job



＃28：新工作概况



＃29：New_Job_Step



＃30：New_Job_Step_General



＃31：New_Job_Steps_Added



＃32：Job_Start_Job_at_Step



＃33：SQL_Job_Execution_Failure



＃34：查看历史



＃35：SQL_Job_Error_Message



＃36：SQL_Job_Execution_Success



＃37：SQL_Job_Success_Message



＃38：Excel_File_Generated



＃39：Command_Comparison