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

I have an SSIS package that exports data to a couple of Excel files for transfer to a third party. To get this to run as a scheduled job on a 64-bit server I understand that I need to set the step as a CmdExec type and call the 32-bit version of DTExec. But I don't seem to be able to get the command right to pass in the connection string for the Excel files.

我有一个SSIS包，它将数据导出到几个Excel文件中，以便转移到第三方。要让它在64位服务器上作为预定任务运行，我理解需要将步骤设置为CmdExec类型，并调用32位版本的DTExec。但是，我似乎无法获得在Excel文件的连接字符串中传递的命令权限。

So far I have this:

到目前为止，我有:

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

This gives me the error: Option "Properties=Excel 8.0;HDR=Yes" is not valid.

这给了我一个错误:选项“属性=Excel 8.0;HDR=Yes”无效。

I've tried a few variations with the Quotation marks but have not been able to get it right yet.

我已经尝试了一些引号的变化，但还没弄对。

Does anyone know how to fix this?

有人知道怎么解决这个问题吗?

UPDATE:

更新:

Thanks for your help but I've decided to go with CSV files for now, as they seem to just work on the 64-bit version.

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

5 个解决方案

#1

This step-by-step example is for others who might stumble upon this question. This example uses SSIS 2005 and uses SQL Server 2005 64-bit edition server to run the job.

这个循序渐进的例子适用于其他可能偶然发现这个问题的人。这个示例使用SSIS 2005，并使用SQL Server 2005 64位版本服务器来运行该作业。

The answer here concentrates only on fixing the error message mentioned in the question. The example will demonstrate the steps to recreate the issue and also the cause of the issue followed by how to fix it.

这里的答案只集中于修复问题中提到的错误消息。这个示例将演示重新创建问题的步骤，以及问题的起因，以及如何修复它。

NOTE: I would recommend using the option of storing the package configuration values in database or using indirect XML configuration with the help of Environment Variables. Also, the steps to create Excel file would be done using a template which would then archived by moving to a different folder. These steps are not discussed in this post. As mentioned earlier, the purpose of this post is to address the error.

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

Let’s proceed with the example. I have also blogged about this answer, which can be found in this link. It is the same answer.

让我们继续这个例子。我也写过这个答案，在这个链接中可以找到。答案是一样的。

Create an SSIS package (Steps to create an SSIS package). This example uses BIDS 2005. I have named the package in the format YYYYMMDD_hhmm in the beginning followed by SO stands for Stack Overflow, followed by the SO question id, and finally a description. I am not saying that you should name your package like this. This is for me to easily refer this back later. Note that I also have a Data Sources named Adventure Works. I will be using Adventure Works data source, which points to AdventureWorks database downloaded from this link. The example uses SQL Server 2008 R2 database. Refer screenshot #1.

创建一个SSIS包(创建一个SSIS包的步骤)。这个例子使用的是2005年的投标。我在开头用yyyyymmdd_hhmm格式命名了这个包，后跟SO表示堆栈溢出，然后是SO问题id，最后是一个描述。我不是说你应该这样命名你的包裹。这是为了便于我以后参考。注意，我还有一个名为Adventure Works的数据源。我将使用冒险工作数据源，它指向从这个链接下载的AdventureWorks数据库。本例使用SQL Server 2008 R2数据库。请参考截图# 1。

In the AdventureWorks database, create a stored procedure named dbo.GetCurrency using the below given script.

在AdventureWorks数据库中，创建一个名为dbo的存储过程。使用下面给定的脚本获取货币。

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

On the package’s Connection Manager section, right-click and select New Connection From Data Source. On the Select Data Source dialog, select Adventure Works and click OK. You should now see the Adventure Works data source under the Connection Managers section.

在包的Connection Manager部分，右键单击并从数据源中选择New Connection。在“选择数据源”对话框中，选择“冒险工程”并单击“确定”。现在，您应该在Connection Managers一节中看到Adventure Works数据源。

On the package’s Connection Managers section, right-click again but this time select New Connection…. This is to create the Excel connection. On the Add SSIS Connection Manager, select EXCEL. On the Excel Connection Manager, enter the path C:\Temp\Template.xls. When we deploy it to the server, we will change this path. I have selected Excel version Microsoft Excel 97-2005 and chose to leave the checkbox First row has column names checked so that the create the Excel file is created column headers. Click OK. Rename the Excel connection to Excel, just to keep it simple. Refer screenshots #2 - #7.

在包的连接管理器部分,再次右键单击但这次选择新连接....这是为了创建Excel连接。在Add SSIS连接管理器上，选择EXCEL。在Excel连接管理器上，输入路径C:\Temp\Template.xls。当我们将它部署到服务器时，我们将更改此路径。我选择了Excel版本的microsoftexcel 97-2005，并选择保留复选框第一行的列名，以便创建Excel文件创建列标题。单击OK。将Excel连接重命名为Excel，以保持简单。参考屏幕截图#2 - #7。

On the package, create the following variable. Refer screenshot #8.

在包中，创建以下变量。请参考截图# 8。

• SQLGetData: This variable is of type String. This will contain the Stored Procedure execution statement. This example uses the value EXEC dbo.GetCurrency
• SQLGetData:该变量类型为String。这将包含存储过程执行语句。这个示例使用值执行器dbo.GetCurrency

Screenshot #9 shows the output of the stored procedure execution statement EXEC dbo.GetCurrency

屏幕截图#9显示了存储过程执行语句EXEC dbo.GetCurrency的输出

On the package’s Control Flow tab, place a Data Flow task and name it as Export to Excel. Refer screenshot #10.

在package的Control Flow选项卡上，放置一个数据流任务，并将其命名为Export to Excel。请参考截图# 10。

Double-click on the Data Flow Task to switch to the Data Flow tab.

双击数据流任务，切换到Data Flow选项卡。

On the Data Flow tab, place an OLE DB Source to connect to the SQL Server data to fetch the data from the stored procedure and name it as SQL. Double-click on the OLE DB Source to bring up the OLE DB Source Editor. On the Connection Manager section, select Adventure Works from the OLE DB connection manager, select SQL command from variable from Data access mode and select the variable User::SQLGetData from the Variable name drop down. On the Columns section, make sure the column names are mapped correctly. Click OK to close the OLE DB Source Editor. Refer screenshots #11 and #12.

在Data Flow选项卡上，放置一个OLE DB源来连接SQL Server数据，从存储过程中获取数据，并将其命名为SQL。双击OLE DB源以打开OLE DB源编辑器。在连接管理器部分，从OLE DB连接管理器中选择冒险工作，从数据访问模式中选择SQL命令，并选择变量用户::SQLGetData从变量名下拉。在Columns部分中，确保正确地映射列名。单击OK关闭OLE DB源编辑器。参考屏幕截图#11和#12。

On the Data Flow tab, place an Excel Destination to insert the data into the Excel file and name it as Excel. Double-click on the Excel Destination to open the Excel Destination Editor. On the Connection Manager section, select Excel from the OLE DB connection manager and select Table or view from Data access mode. At this point, we don’t have an Excel because while creating the Excel connection manager, we simply specified the path but never created the file. Hence, there won’t be any values in the drop down Name of the Excel sheet. So, click the New… button (the second New one) to create a new Excel sheet. On the Create Table window, BIDS automatically provide a create sheet based on the incoming data source. You can change the values according to your preferences. I will simply click OK by retaining the default value. The name of the sheet will be populated in the drop down Name of the Excel sheet. The name of the sheet is taken from the task name, here in this case the Excel Destination, which we have named it as Excel. On the Mappings section, make sure the column names are mapped correctly. Click OK to close the Excel Destination Editor. Refer screenshots #13 - #16.

在Data Flow选项卡上，放置一个Excel目的地，将数据插入Excel文件，并将其命名为Excel。双击Excel目的地，打开Excel目标编辑器。在连接管理器部分，从OLE DB连接管理器中选择Excel，并从数据访问模式中选择表或视图。此时，我们没有Excel，因为在创建Excel连接管理器时，我们只是指定了路径，但从未创建文件。因此，在Excel表的下拉名中不会有任何值。因此，单击新的…按钮(第二个新按钮)来创建新的Excel表。在Create Table窗口中，投标自动提供基于传入数据源的创建表。您可以根据自己的喜好更改值。我只需通过保留默认值来单击OK。工作表的名称将在Excel工作表的下拉名称中填充。工作表的名称取自任务名称，这里是Excel目的地，我们将它命名为Excel。在Mappings部分，确保正确地映射列名。单击OK关闭Excel目标编辑器。参考屏幕截图#13 - #16。

Once the data flow task is configured, it should look like as shown in screenshot #17.

一旦配置了数据流任务，它应该像屏幕截图#17所示。

Execute the package by pressing F5. Screenshots #18 - #21 show the successful execution of the package in both Control Flow and Data Flow Task. Also, the file is generated in the path C:\Temp\Template.xls provided in the Excel connection and the data shown in the stored procedure execution output matches with the data written to the file.

按F5执行包。屏幕快照#18 - #21显示了在控制流和数据流任务中成功执行包的情况。此外，该文件是在路径C:\Temp\模板中生成的。Excel连接中提供的xls和存储过程执行输出中显示的数据与写入文件的数据相匹配。

The package developed on my local machine in the folder path C:\Learn\Learn.VS2005\Learn.SSIS. Now, we need to deploy the files on to the Server that hosts the 64-bit version of the SQL Server to schedule a job. So, the folder on the server would be D:\SSIS\Practice. Copy the package file (.dtsx) from the local machine and paste it in the server folder. Also, in order for the package to run correctly, we need to have the Excel spreadsheet present on the server. Otherwise, the validation will fail. Usually, I create a Template folder that will contain the empty Excel spreadsheet file that matches the output. Later, during run time I will change the Excel output path to a different location using package configuration. For this example, I am going to keep it simple. So, let’s copy the Excel file generated in the local machine in the path C:\Temp\Template.xls to the server location D:\SSIS\Practice. I want the SQL job to generate the file in the name Currencies.xls. So, rename the file Template.xls to Currencies.xls. Refer screenshot #22.

在我的本地机器上开发的包路径C:\学习。vs2005 \学习。ssis。现在，我们需要将文件部署到承载SQL服务器64位版本的服务器上，以便调度作业。因此，服务器上的文件夹将是D:\SSIS\实践。从本地计算机复制包文件(.dtsx)并将其粘贴到服务器文件夹中。此外，为了使包正确运行，我们需要在服务器上显示Excel电子表格。否则，验证将失败。通常，我创建一个模板文件夹，其中将包含与输出匹配的空Excel电子表格文件。稍后，在运行时，我将使用包配置将Excel输出路径更改为不同的位置。对于本例，我将保持简单。因此，让我们在路径C:\Temp\模板中复制在本地机器中生成的Excel文件。xls到服务器位置D:\SSIS\实践。我希望SQL作业以curren .xls的名称生成文件。因此，重命名文件模板。xls Currencies.xls。请参考截图# 22。

To show that I am indeed going to run the job on the server in a 64-bit edition of SQL Server, I executed the command SELECT @@version on the SQL Server and screenshot #23 shows the results.

为了显示我确实要在一个64位的SQL server中运行服务器上的作业，我在SQL服务器上执行了命令SELECT @version，屏幕截图#23显示了结果。

We will use Execute Package Utility (dtexec.exe) to generate the command line parameters. Log into the server which will run the SSIS package in an SQL job. Double-click on the package file, this will bring the Execute Package Utility. On the General section, select File system from Package source. Click on the Ellipsis and browse to the package path. On the Connection Managers section, select Excel and change the path inside the Excel file from C:\Temp\Template.xls to D:\SSIS\Practice\Currencies.xls. The changes made in the Utility will generate a command line accordingly on the Command Line section. On the Command Line section, copy the Command line that contains all the necessary parameters. We are not going to execute the package from here. Click Close. Refer screenshots #24 - #26.

我们将使用Execute Package Utility (dtexec.exe)来生成命令行参数。登录到将在SQL作业中运行SSIS包的服务器。双击包文件，这将带来执行包实用程序。在通用部分，从包源代码中选择File system。单击省略号并浏览到包路径。在连接管理器部分，选择Excel并从C:\Temp\Template中更改Excel文件中的路径。xls D:\ SSIS \ \ Currencies.xls练习。在实用程序中所做的更改将在命令行部分相应地生成命令行。在命令行部分，复制包含所有必要参数的命令行。我们不会在这里执行这个包。单击Close。参考屏幕截图#24 - #26。

Next, we need to set up a job to run the SSIS package. We cannot choose SQL Server Integration Services Package type because that will run under 64-bit and won’t find the Excel connection provider. So, we have to run it as Operating System (CmdExec) job type. Go to SQL Server Management Studio and connect to the Database Engine. Expand SQL Server Agent and right-click on Jobs node. Select New Job…. On the General section of the Job Properties window, provide the job name as 01_SSIS_Export_To_Excel, Owner will be the user creating the job. I have a Category named SSIS, so I will select that but the default category is [Uncategorized (Local)] and provide a brief description. On the Steps section, click New… button. This will bring Job Step properties. On the General section of the Job Step properties, provide Step name as Export to Excel, Select type Operating system (CmdExec), leave the default Run as account as SQL Server Agent Service Account and provide the following Command. Click OK. On the New Job window, Click OK. Refer screenshots #27 - #31.

接下来，我们需要设置一个作业来运行SSIS包。我们不能选择SQL Server Integration Services包类型，因为它将在64位下运行，并且不会找到Excel连接提供程序。因此，我们必须运行它作为操作系统(CmdExec)作业类型。转到SQL Server Management Studio并连接到数据库引擎。展开SQL Server代理并右键单击Jobs节点。选择新工作....在作业属性窗口的General部分，提供作业名为01_SSIS_Export_To_Excel, Owner将是创建作业的用户。我有一个名为SSIS的类别，所以我将选择它，但是默认的类别是[未分类的(本地的)]，并提供一个简短的描述。在步骤部分，单击New…按钮。这将带来作业步骤属性。在作业步骤属性的一般部分中，提供步骤名作为导出到Excel，选择type Operating system (CmdExec)，将默认运行作为SQL Server Agent服务帐户，并提供以下命令。单击OK。在新的作业窗口中，单击OK。参考屏幕截图#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

The new job should appear under SQL Server Agent –> Jobs node. Right-click on the newly created job 01_SSIS_Export_To_Excel and select Start Job at Step…, this will commence the job execution. The job will fail as expected because that is the context of this issue. Click Close to close the Start Jobs dialog. Refer screenshots #32 and #33.

新的作业应该出现在SQL Server Agent - > Jobs节点下。右键单击新创建的作业01_SSIS_Export_To_Excel，然后在步骤……处选择Start job，这将开始作业执行。工作会像预期的那样失败，因为这是问题的背景。单击Close关闭“开始作业”对话框。参考屏幕截图#32和#33。

Let’s take a look at what happened. Go to SQL Server Agent and Jobs node. Right-click on the job 01_SSIS_Export_To_Excel and select View History. This will bring the Log File Viewer window. You can notice that the job failed. Expand the node near the red cross and click on the line that Step ID value of 1. At the bottom section, you can see the error message Option “8.0;HDR=YES’;” is not valid. Click Close to close the Log File Viewer window. Refer screenshots #34 and #35.

让我们看看发生了什么。转到SQL Server代理和作业节点。右键单击作业01_SSIS_Export_To_Excel并选择View History。这将带来日志文件查看器窗口。你会发现工作失败了。展开红十字会附近的节点，并单击步骤ID值为1的行。在底部部分，您可以看到错误消息选项“8.0;HDR=YES”无效。单击Close关闭日志文件查看器窗口。参考屏幕截图#34和#35。

Now, right-click on the job and select Properties to open the Job Properties. You can also double-click on the job to bring the Job Properties window. Click on the Steps on the left section. and click Edit. Replace the command with the following command and click OK. Click OK on the Job Properties to close the window. Right-click on the job 01_SSIS_Export_To_Excel and select Start Job at Step…, this will commence the job execution. The job will fail execute successfully. Click Close to close the Start Jobs dialog. Let’s take a look at the history. Right-click on the job 01_SSIS_Export_To_Excel and select View History. This will bring the Log File Viewer window. You can notice that the job succeeded during the second run. Expand the node near the green tick cross and click on the line that Step ID value of 1. At the bottom section, you can see the message Option The step succeeded. Click Close to close the Log File Viewer window. The file D:\SSIS\Practice\Currencies.xls will be successfully populated with the data. If you execute the job successfully multiple times, the data will get appended to the file and you will find more data. As I mentioned earlier, this is not the right-way to generate the files. This example was created to demonstrate a fix for this issue. Refer screenshots #36 - #38.

现在，右键单击作业并选择Properties来打开作业属性。您还可以双击该作业以获得作业属性窗口。单击左边部分的步骤。,然后单击编辑。用以下命令替换该命令并单击OK。单击作业属性上的OK以关闭窗口。右键单击作业01_SSIS_Export_To_Excel，然后在步骤……处选择Start job，这将开始作业执行。该作业将无法成功执行。单击Close关闭“开始作业”对话框。让我们看一看历史。右键单击作业01_SSIS_Export_To_Excel并选择View History。这将带来日志文件查看器窗口。您可以注意到作业在第二次运行中成功了。展开绿色标记十字附近的节点，单击步骤ID值为1的行。在底部部分，您可以看到步骤成功的消息选项。单击Close关闭日志文件查看器窗口。文件D:\ SSIS \实践\货币。xls将使用数据成功填充。如果您多次成功执行作业，数据将被附加到文件中，您将找到更多的数据。如前所述，这不是生成文件的正确方式。这个示例是为了演示这个问题的解决方案而创建的。参考屏幕截图#36 - #38。

Screenshot #39 shows the differences between the working and the non-working command line arguments. The one on the right is the working command line and the left one is incorrect. It required another double quotes with backslash escape sequence to fix the error. There could be other ways to fix this well but this option seems to work.

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

Thus, the example demonstrated a way to fix the command line argument issue while accessing Excel data source from an SSIS package that is deployed on a 64-bit server.

因此，这个示例演示了在从部署在64位服务器上的SSIS包访问Excel数据源时修复命令行参数问题的方法。

Hope that helps someone.

希望可以帮助别人。

Screenshots:

截图:

#1: Solution_Explorer

# 1:Solution_Explorer

#2: New_Connection_Data_Source

# 2:New_Connection_Data_Source

#3: Select_Data_Source

# 3:Select_Data_Source

#4: New_Connection

# 4:New_Connection

#5: Add_SSIS_Connection_Manager

# 5:Add_SSIS_Connection_Manager

#6: Excel_Connection_Manager

# 6:Excel_Connection_Manager

#7: Connection_Managers

# 7:Connection_Managers

#8: Variables

# 8:变量

#9: Stored_Procedure_Output

# 9:Stored_Procedure_Output

#10: Control_Flow

# 10:Control_Flow

#11: OLE_DB_Source_Connections_Manager

# 11:OLE_DB_Source_Connections_Manager

#12: OLE_DB_Source_Columns

# 12:OLE_DB_Source_Columns

#13: Excel_Destination_Editor_New

# 13:Excel_Destination_Editor_New

#14: Excel_Destination_Create_Table

# 14:Excel_Destination_Create_Table

#15: Excel_Destination_Edito

# 15:Excel_Destination_Edito

#16: Excel_Destination_Mappings

# 16:Excel_Destination_Mappings

#17: Data_Flow

# 17:Data_Flow

#18: Successful_Package_Execution_Control

# 18:Successful_Package_Execution_Control

#19: Successful_Package_Execution_Data_Flow

# 19:Successful_Package_Execution_Data_Flow

#20: C_Temp_File_Created

# 20:C_Temp_File_Created

#21: Data_Populated

# 21:Data_Populated

#22: File_On_Server

# 22:File_On_Server

#23: SQL_Server_Version

# 23:SQL_Server_Version

#24: Execute_Package_Utility_General

# 24:Execute_Package_Utility_General

#25: Execute_Package_Utility_Connection_Managers

# 25:Execute_Package_Utility_Connection_Managers

#26: Execute_Package_Utility_Command_Line

# 26:Execute_Package_Utility_Command_Line

#27: Job_New_Job

# 27:Job_New_Job

#28: New_Job_General

# 28:New_Job_General

#29: New_Job_Step

# 29:New_Job_Step

#30: New_Job_Step_General

# 30:New_Job_Step_General

#31: New_Job_Steps_Added

# 31:New_Job_Steps_Added

#32: Job_Start_Job_at_Step

# 32:Job_Start_Job_at_Step

#33: SQL_Job_Execution_Failure

# 33:SQL_Job_Execution_Failure

#34: View_History

# 34:View_History

#35: SQL_Job_Error_Message

# 35:SQL_Job_Error_Message

#36: SQL_Job_Execution_Success

# 36:SQL_Job_Execution_Success

#37: SQL_Job_Success_Message

# 37:SQL_Job_Success_Message

#38: Excel_File_Generated

# 38:Excel_File_Generated

#39: Command_Comparison

# 39:Command_Comparison

#2

You can use an Excel connection in 64bit environment. Go to the package configuration properties.

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

Debugging -> Debugging Options -> Run64BtRuntime -> change to False In addition if you use SQL Agent go to the job step properties and then check the 32 bit runtime.

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

note: this only applies to debugging within Visual Studio...

注意:这只适用于Visual Studio中的调试…

#3

There is no 64-bit Jet OLEDB provider, so you can't access Excel files from 64-bit SSIS.

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

However, you can use 32-bit SSIS even on 64-bit server. It is already installed when you installed 64-bit version, and all you need to do is run the 32-bit DTEXEC.EXE - the one installed Program Files (x86)\Microsoft Sql Server\90\Dts\Binn (replace 90 with 100 if you are using SSIS 2008).

但是，即使在64位服务器上，也可以使用32位的SSIS。在安装64位版本时已经安装了它，您需要做的就是运行32位的DTEXEC。EXE -安装程序文件(x86)\Microsoft Sql Server\90\Dts\Binn(如果使用SSIS 2008，则用100替换90)。

#4

Unless it's a business requirement, I suggest you move the connection string from the command line to the package and use a package configuration to define the path to the Excel file (in order not to hard-code it). This will make it easier to maintain.

除非是业务需求，否则我建议您将连接字符串从命令行移动到包，并使用包配置来定义Excel文件的路径(为了不硬编码它)。这将使维护变得更容易。

1. Define a variable @ExcelPath.
2. 定义一个变量@ExcelPath。
3. Use connection's Expression property to construct a connection string - an example: "Data Source=" + @[User::FilePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;"
4. 使用连接的表达式属性来构造一个连接字符串——一个示例:“Data Source=”+ @[User::FilePath] +;Provider=Microsoft.Jet.OLEDB.4.0;扩展属性=dBASE IV;
5. Assign a value to @ExcelPath in the package configuration.
6. 在包配置中为@ExcelPath赋值。

---

Take a closer look at the connection string above. It's taken from a working package. I'm not sure about this, but maybe you don't need any quotes at all (the ones above are only there because the expression editor requires them).

仔细查看上面的连接字符串。它是从一个工作包里拿出来的。对此我不太确定，但是您可能根本不需要任何引号(上面的这些只是因为表达式编辑器需要它们)。

---

I have also had some problems with SSIS on 64-bit SQL Server 2005. That post from my blog does not answer your question, but it is somewhat related so I am posting the link.

我在64位SQL Server 2005上的SSIS也有一些问题。我博客上的这篇文章并没有回答你的问题，但它与你的问题有一定的关系，所以我将发布这个链接。

#5

I kinda did what Dr Zim did but I copied the DTExec file C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe to C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ folder but named the 32 bit one to DTExec32.exe

我有点做结汇博士做了什么但我复制DTExec文件C:\Program Files (x86)\Microsoft SQL Server \ 90 \ DTS \ Binn \ DTExec。exe C:\Program Files\Microsoft SQL Server \ 90 \ DTS \ Binn \文件夹但DTExec32.exe命名为32位的

then I was able to run my SSIS script through a stored proc:

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

set @params = '/set \package.variables[ImportFilename].Value;"\"' + @FileName + '\"" '
set @cmd =  'dtexec32 /SQ "' + @packagename + ' ' + @params + '"'
--DECLARE @returncode int
exec master..xp_cmdshell @cmd
--exec @returncode = master..xp_cmdshell @cmd
--select @returncode