一个用windows验证方式验证的ssis包，手动执行可以成功。但是在sa下用xp_cmdshell调用dtexec就失败。我怀疑是安全上下文的问题，咋整啊Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 4:23:13 AM
Progress: 2007-11-30 04:23:14.20
Source: Transfer data to tempdb
Validating: 0% complete
End Progress
Progress: 2007-11-30 04:23:14.23
Source: Transfer data to tempdb
Validating: 50% complete
End Progress
Error: 2007-11-30 04:23:14.29
Code: 0xC0202009
Source: Transfer data to tempdb CCRCLog Destination [2881]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E09 Description: "SELECT permission denied on object 'CCRCLog', database 'tempdb', schema 'dbo'.".
End Error
Error: 2007-11-30 04:23:14.29
Code: 0xC0202040
Source: Transfer data to tempdb CCRCLog Destination [2881]
Description: Failed to open a fastload rowset for "[dbo].[CCRCLog]". Check that the object exists in the database.
End Error
Error: 2007-11-30 04:23:14.29
Code: 0xC004706B
Source: Transfer data to tempdb DTS.Pipeline
Description: "component "CCRCLog Destination" (2881)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Progress: 2007-11-30 04:23:14.29
Source: Transfer data to tempdb
Validating: 100% complete
End Progress
Error: 2007-11-30 04:23:14.29
Code: 0xC004700C
Source: Transfer data to tempdb DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2007-11-30 04:23:14.29
Code: 0xC0024107
Source: Transfer data to tempdb
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 4:23:13 AM
Finished: 4:23:14 AM
Elapsed: 0.859 seconds
NULL
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 4:23:13 AM
Progress: 2007-11-30 04:23:14.20
Source: Transfer data to tempdb
Validating: 0% complete
End Progress
Progress: 2007-11-30 04:23:14.23
Source: Transfer data to tempdb
Validating: 50% complete
End Progress
Error: 2007-11-30 04:23:14.29
Code: 0xC0202009
Source: Transfer data to tempdb CCRCLog Destination [2881]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E09 Description: "SELECT permission denied on object 'CCRCLog', database 'tempdb', schema 'dbo'.".
End Error
Error: 2007-11-30 04:23:14.29
Code: 0xC0202040
Source: Transfer data to tempdb CCRCLog Destination [2881]
Description: Failed to open a fastload rowset for "[dbo].[CCRCLog]". Check that the object exists in the database.
End Error
Error: 2007-11-30 04:23:14.29
Code: 0xC004706B
Source: Transfer data to tempdb DTS.Pipeline
Description: "component "CCRCLog Destination" (2881)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Progress: 2007-11-30 04:23:14.29
Source: Transfer data to tempdb
Validating: 100% complete
End Progress
Error: 2007-11-30 04:23:14.29
Code: 0xC004700C
Source: Transfer data to tempdb DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2007-11-30 04:23:14.29
Code: 0xC0024107
Source: Transfer data to tempdb
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 4:23:13 AM
Finished: 4:23:14 AM
Elapsed: 0.859 seconds
NULL
net use \\主机 密码 /USER:administrator
注：以上纯属猜测，可以试试
忘了是怎么产生的了，好像是修改了什么东西。
不知道有沒有用。
楼主用以下方法查一下原因:SQL Server 外围应用配置器—功能介面状态（启用xp_cmdshell）
--
把登陆模式改为混合模式。。
When you try running packages with exec xp_cmdshell 'dtexec…' from the sql 2000 instance on db7 they should run under the sql agent service account, which is the iibsqladmin account, so I'm not exactly sure why it wouldn't work, but I don't think restarting the services will fix it, as it's probably permissions related.
散分啦。