SSIS导数据到excel现有模板, 部署到sql server无法运行
近期做一个利用SSIS 将SQL Server的数据导入到已存在的excel模板文件中。
在本地运行时没有任何问题， 但是部署到SQL SERVER上去后 发现无法运行并报错如下
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)). Details: at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType) at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType) at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props, Boolean bNewObj) at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.Activator.CreateInstance(Type type)
这里排除了以下问题：但是依旧无法解决该问题
1. 安装了相同版本的Microsoft office
2. 以32位运行SSIS
最终在google找到如下解决方法:
CMD 运行
dcomcnfg
找到 组件服务> 计算机> 我的电脑> DCOM Config > Microsoft Excel Application
右键Microsoft Excel Application 属性, 切换到 安全> 并如下图所设, 添加相对应的账户以及NT SERVICE\MSSQLSERVER 账户
如果只设置到这一步, 将会报另一错误
There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. Details: at Microsoft.Office.Interop.Excel.Workbooks.Add(Object Template)
解决方案: 继续回到COM+ Config , 找到Microsoft Excel Application 右键 属性>> 切换到[身份], 使用指定账户运行
坑: 如使用 The interactive user(交互式用户) 将会出现退出远程或者logout 账户时 将出现另一个错误.
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 8000401a The server process could not be started because the configured identity is incorrect. Check the username and password. (Exception from HRESULT: 0x8000401A). Details: at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType) at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType) at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props, Boolean bNewObj) at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.Activator.CreateInstance(Type type)
