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

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

到目前为止，我有这个：

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

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

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

UPDATE:

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

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

好。

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

好。

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

好。

让我们继续该示例。我也写了关于这个答案的博客，可以在此链接中找到。答案是一样的。

好。

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

好。

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

好。

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

好。

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

好。

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

好。

好。

屏幕快照＃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工作表的下拉名称中将没有任何值。因此，单击" New…"按钮(第二个" New"按钮)以创建一个新的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。

好。

新作业应显示在" 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：Successful_Package_Execution_Data_Flow

好。

好。

＃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：New_Job_General

好。

好。

＃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

好。

好。

好。

您可以在64位环境中使用Excel连接。
转到程序包配置属性。

调试->调试选项-> Run64BtRuntime->更改为False
此外，如果使用SQL Agent，请转到作业步骤属性，然后检查32位运行时。

注意：这仅适用于Visual Studio中的调试...

没有64位Jet OLEDB提供程序，因此您无法从64位SSIS访问Excel文件。

但是，即使在64位服务器上，也可以使用32位SSIS。当您安装64位版本时，它已经安装，并且您需要做的就是运行32位DTEXEC.EXE-一个已安装的Program Files (x86)\\Microsoft Sql Server\\90\\Dts\\Binn(如果使用SSIS 2008，则将90替换为100)。

除非有业务要求，否则建议您将连接字符串从命令行移至程序包，并使用程序包配置定义Excel文件的路径(以免对其进行硬编码)。这将使其易于维护。

仔细看看上面的连接字符串。它是从工作包中提取的。我对此不确定，但是也许您根本不需要任何引号(上面的引号仅存在，因为表达式编辑器需要它们)。

我在64位SQL Server 2005上的SSIS也遇到了一些问题。我博客中的帖子没有回答您的问题，但是有些相关，因此我发布了链接。

我有点像Zim博士所做的，但是我将DTExec文件C:\\Program Files (x86)\\Microsoft SQL Server\\90\\DTS\\Binn\\DTExec.exe复制到了C:\\Program Files\\Microsoft SQL Server\\90\\DTS\\Binn\\文件夹，但是将32位文件名命名为DTExec32.exe。

然后我就可以通过存储的proc运行我的SSIS脚本：