I have an SSIS package which reads an Excel File (Data Flow Source) and transfer the data to SQL Server using OLEDB Destination Data Flow Item. The OLEDB Connection Manager used for the destination is configured to use Windows Authentication. The package works fine on my development machine. But when I open the same package on another machine and try to execute it gives the following error in Validation phase
我有一个SSIS包，它读取Excel文件(数据源)并使用OLEDB目标数据流项将数据传输到SQL Server。用于目标的OLEDB连接管理器被配置为使用Windows身份验证。这个包在我的开发机器上运行良好。但是当我在另一台机器上打开相同的包并尝试执行时，在验证阶段会出现以下错误
Error: 0xC020801C at DFT_NSOffers, Source - 'Subscription Offers$' 1 [347]: The AcquireConnection method call to the connection manager "ExcelConnection_NSOffers" failed with error code 0xC0202009. Error: 0xC0047017 at DFT_NSOffers, DTS.Pipeline: component "Source - 'Subscription Offers$' 1" (347) failed validation and returned error code 0xC020801C. Error: 0xC004700C at DFT_NSOffers, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at DFT_NSOffers: There were errors during task validation
错误:在DFT_NSOffers的0xC020801C，源- '订阅提供$' 1[347]:对连接管理器“ExcelConnection_NSOffers”的AcquireConnection方法调用失败，错误代码为0xC0202009。错误:DFT_NSOffers, DTS, 0xC0047017。管道:组件“源-”订阅提供$“1”(347)失败的验证和返回的错误代码0xC020801C。错误:DFT_NSOffers, DTS的0xC004700C。管道:一个或多个组件验证失败。错误:在DFT_NSOffers处的0xC0024107:任务验证期间有错误
I'm using SQL Server 2005 (Version - 9.0.1399)
我使用的是SQL Server 2005(版本- 9.0.1399)
How do I fix this? Do I need to install any other component or service pack?
我怎么修复这个?我是否需要安装其他组件或服务包?
12 个解决方案
#1
17
I don't think it's 64/32 bit error. My Dev machine and DB server are 32bit. But I could make it work. I had to set Delay Validation Property of Data Flow tasks to TRUE.
我不认为是64/32位误差。我的开发机器和DB服务器是32位的。但我可以让它工作。我必须将数据流任务的延迟验证属性设置为TRUE。
#2
3
64/32 bit error? I found this as a problem as my dev machine was 32bit and the production server 64bit. If so, you may need to call the 32bit runtime directly from the command line.
64/32位错误?我发现这是一个问题，因为我的开发机器是32位的，而生产服务器是64位的。如果是，您可能需要直接从命令行调用32位运行时。
This link says it better (No 64bit JET driver): http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/da076e51-8149-4948-add1-6192d8966ead/
这个链接说得更好(没有64位的JET driver): http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/da076e51-8149-4948-add1-6192d8966ead/
#3
2
I was finally able to resolve the "Excel connection issue" in my case it was not a 64 bit issue like some of them had encounterd, I noticed the package worked fine when i didnt enable the package configuration, but i wanted my package to run with the configuration file, digging further into it i noticed i had selected all the properties that were available, I unchecked all and checked only the ones that I needed to store in the package configuration file. and ta dha it works :)
我终于能够解决“Excel连接问题”对于我来说这是一个64位的问题不像有些夜晚人行道,我注意到包工作得很好,当我没有使包配置,但我希望我的包运行配置文件,进一步挖掘到它我注意到我有选择所有可用的属性,我不,只检查我需要存储在包的配置文件。它能起作用
#4
2
Hi This can be solved by changing the prorperty of the project in the solution explorer then give "false" to 64bit runtime option
你好，这可以通过在解决方案资源管理器中更改项目的prorperty来解决，然后给64位运行时选项“false”
#5
1
For me, I was accessing my XLS file from a network share. Moving the file for my connection manager to a local folder fixed the issue.
对我来说，我是从网络共享访问XLS文件的。将连接管理器的文件移动到本地文件夹解决了这个问题。
#6
1
In my case the problem was the 32/64 bit driver which I solved by configuring the properties of the sql server job:
在我的例子中，问题是我通过配置sql server作业的属性解决的32/64位驱动程序:
#7
1
I was also getting the same error and it simply got resolved after installing the MS offices driver and Execute the job in 32 Bit DTEXEC. Now it works fine.
我也得到了相同的错误，它只是在安装MS office驱动程序并在32位DTEXEC中执行任务之后得到解决。现在工作正常。
You can get the setup from below.
您可以从下面获得设置。
https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734
https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734
#8
0
I reset the delay validation property to true on Excel connection manager and its worked for me. Lets give a try!!!
在Excel连接管理器上，我将delay validation属性重置为true，它对我来说是有效的。让我们给一试! ! !
#9
0
I had similar issue just that excel was the destination in my case instead of source as in the case of the original question/issue. I have spent hours to resolve this issue but looks like finally Soniya Parmar saved the day for me. I have set job and let it run for few iterations already and all is good now. As per her suggestion I set up the delay validation of the Excel connection manager to 'True. Thanks Soniya
我也遇到过类似的问题，只是在我的案例中excel是目标，而不是原始问题的源。我花了好几个小时来解决这个问题，但看起来最后桑尼娅·帕玛尔为我挽救了这一天。我已经设置了作业，并且让它运行了很少的迭代，现在一切都很好。按照她的建议，我将Excel连接管理器的延迟验证设置为“True”。由于Soniya
#10
0
Setting RetainSameConnection property to True for Excel manager Worked for me .
为Excel经理设置RetainSameConnection属性为True曾为我工作过。
#11
0
I had similar issue, trying to load data from Excel spreadsheet; and was running on WinX64. So I went VS BI`s project properties: Configuration Properties \ Dbugging and Switch Run64BitRuntime from True to False. It worked.
我也遇到过类似的问题，试图从Excel电子表格中加载数据;在WinX64上运行。所以我去VS BI的项目属性:Configuration properties \ Dbugging和Switch Run64BitRuntime from True to False。它工作。
#12
-5
In order to resolve this issue make all your data flow tasks in one sequence. It means it should not execute parallel. One data flow task sequence should contain only one data flow task and for this another data flow task as sequence.
为了解决这个问题，将所有的数据流任务放在一个序列中。这意味着它不应该并行执行。一个数据流任务序列应该只包含一个数据流任务，而另一个数据流任务应该作为序列。
Ex:-
例:-
#1
17
I don't think it's 64/32 bit error. My Dev machine and DB server are 32bit. But I could make it work. I had to set Delay Validation Property of Data Flow tasks to TRUE.
我不认为是64/32位误差。我的开发机器和DB服务器是32位的。但我可以让它工作。我必须将数据流任务的延迟验证属性设置为TRUE。
#2
3
64/32 bit error? I found this as a problem as my dev machine was 32bit and the production server 64bit. If so, you may need to call the 32bit runtime directly from the command line.
64/32位错误?我发现这是一个问题，因为我的开发机器是32位的，而生产服务器是64位的。如果是，您可能需要直接从命令行调用32位运行时。
This link says it better (No 64bit JET driver): http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/da076e51-8149-4948-add1-6192d8966ead/
这个链接说得更好(没有64位的JET driver): http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/da076e51-8149-4948-add1-6192d8966ead/
#3
2
I was finally able to resolve the "Excel connection issue" in my case it was not a 64 bit issue like some of them had encounterd, I noticed the package worked fine when i didnt enable the package configuration, but i wanted my package to run with the configuration file, digging further into it i noticed i had selected all the properties that were available, I unchecked all and checked only the ones that I needed to store in the package configuration file. and ta dha it works :)
我终于能够解决“Excel连接问题”对于我来说这是一个64位的问题不像有些夜晚人行道,我注意到包工作得很好,当我没有使包配置,但我希望我的包运行配置文件,进一步挖掘到它我注意到我有选择所有可用的属性,我不,只检查我需要存储在包的配置文件。它能起作用
#4
2
Hi This can be solved by changing the prorperty of the project in the solution explorer then give "false" to 64bit runtime option
你好，这可以通过在解决方案资源管理器中更改项目的prorperty来解决，然后给64位运行时选项“false”
#5
1
For me, I was accessing my XLS file from a network share. Moving the file for my connection manager to a local folder fixed the issue.
对我来说，我是从网络共享访问XLS文件的。将连接管理器的文件移动到本地文件夹解决了这个问题。
#6
1
In my case the problem was the 32/64 bit driver which I solved by configuring the properties of the sql server job:
在我的例子中，问题是我通过配置sql server作业的属性解决的32/64位驱动程序:
#7
1
I was also getting the same error and it simply got resolved after installing the MS offices driver and Execute the job in 32 Bit DTEXEC. Now it works fine.
我也得到了相同的错误，它只是在安装MS office驱动程序并在32位DTEXEC中执行任务之后得到解决。现在工作正常。
You can get the setup from below.
您可以从下面获得设置。
https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734
https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734
#8
0
I reset the delay validation property to true on Excel connection manager and its worked for me. Lets give a try!!!
在Excel连接管理器上，我将delay validation属性重置为true，它对我来说是有效的。让我们给一试! ! !
#9
0
I had similar issue just that excel was the destination in my case instead of source as in the case of the original question/issue. I have spent hours to resolve this issue but looks like finally Soniya Parmar saved the day for me. I have set job and let it run for few iterations already and all is good now. As per her suggestion I set up the delay validation of the Excel connection manager to 'True. Thanks Soniya
我也遇到过类似的问题，只是在我的案例中excel是目标，而不是原始问题的源。我花了好几个小时来解决这个问题，但看起来最后桑尼娅·帕玛尔为我挽救了这一天。我已经设置了作业，并且让它运行了很少的迭代，现在一切都很好。按照她的建议，我将Excel连接管理器的延迟验证设置为“True”。由于Soniya
#10
0
Setting RetainSameConnection property to True for Excel manager Worked for me .
为Excel经理设置RetainSameConnection属性为True曾为我工作过。
#11
0
I had similar issue, trying to load data from Excel spreadsheet; and was running on WinX64. So I went VS BI`s project properties: Configuration Properties \ Dbugging and Switch Run64BitRuntime from True to False. It worked.
我也遇到过类似的问题，试图从Excel电子表格中加载数据;在WinX64上运行。所以我去VS BI的项目属性:Configuration properties \ Dbugging和Switch Run64BitRuntime from True to False。它工作。
#12
-5
In order to resolve this issue make all your data flow tasks in one sequence. It means it should not execute parallel. One data flow task sequence should contain only one data flow task and for this another data flow task as sequence.
为了解决这个问题，将所有的数据流任务放在一个序列中。这意味着它不应该并行执行。一个数据流任务序列应该只包含一个数据流任务，而另一个数据流任务应该作为序列。
Ex:-
例:-