Hi, We have a SSIS package which refreshes an Excel 2007 file using a script task - references the Microsoft.Office.Interop.Excel.dll Runs Ok on 32-bit dev PC - returns this error on server: "Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154. at ST_351c98ccd83b4e988e50f7e36003277f.vbproj.ScriptMain.RefreshExcel() at ST_351c98ccd83b4e988e50f7e36003277f.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()" -------------------------------------------------------------------------------------------------------------- Have installed VSTO and Microsoft Office 2007 Data Connectivity Components - got a "file or assembly not found" error. Then used gacutil to load the dll into GAC - this resolved the "file or assembly not found" error, but then resulted in the error above. Have also designed the package with "Run64BitRuntime" set to false. Server spec: Server 2008 R2; SQL2008 R2 ; .Net Framework 4 Any suggestions much appreciated. MattMattF

Retry after copying this DLL to ...:\Program Files\Microsoft SQL Server\100\DTS\Binn Besides, Run64BitRuntime has no effect outside of BIDS. If the above does not help, please post the code from the Script Task.Arthur My Blog

You could try running the package in 32-bit mode. As Arthur said the Run64BitRuntime setting is just a designer option. When running on a server the simplest way is to reference the 32-bit version of DtExec - "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtExec.exe" /F "C:\MyPackage.dtsx" If that works it clearly indicates that it is a x64 vs x86 issue. I assume installing VSTO includes the PIA install. The Data Connectivity components are something different though, they certainly do not cover the PIA. http://www.microsoft.com/downloads/en/details.aspx?FamilyID=59daebaa-bed4-4282-a28c-b864d8bfa513&displaylang=enhttp://www.sqlis.com | http://www.konesans.com

Thanks for the info - I had run it in design mode on the server (with Run64BitRuntime set to Off) and got the error. Had also installed the PIA, which showed in the event log as installing successfully (needed to check this as the window showing install status just disappears with no confirmation). Tried your suggestion of running the package using DtExec on the server with the same error occurring. Posted script task code below, but I do not think this is the problem as it fails before trying to execute (eg. no breakpoints are reached). Script Task code: Public Sub Main() RefreshExcel() Dts.TaskResult = ScriptResults.Success End Sub Public Sub RefreshExcel() Dim excel As New Microsoft.Office.Interop.Excel.Application Dim wb As Microsoft.Office.Interop.Excel.Workbook Dim strDSN As String Try strDSN = Dts.Variables("strExcelFilePath").Value.ToString wb = excel.Workbooks.Open(strDSN) wb.RefreshAll() wb.Save() wb.Close() excel.Quit() Runtime.InteropServices.Marshal.ReleaseComObject(excel) Catch ex As Exception Throw New Exception End Try End Sub ThanksMattF

Thanks for the info - I had run it in design mode on the server (with Run64BitRuntime set to Off) and got the error. Had also installed the PIA, which showed in the event log as installing successfully (needed to check this as the window showing install status just disappears with no confirmation). Tried your suggestion of running the package using DtExec on the server with the same error occurring. Posted script task code below, but I do not think this is the problem as it fails before trying to execute (eg. no breakpoints are reached). Script Task code: Public Sub Main() RefreshExcel() Dts.TaskResult = ScriptResults.Success End Sub Public Sub RefreshExcel() Dim excel As New Microsoft.Office.Interop.Excel.Application Dim wb As Microsoft.Office.Interop.Excel.Workbook Dim strDSN As String Try strDSN = Dts.Variables("strExcelFilePath").Value.ToString wb = excel.Workbooks.Open(strDSN) wb.RefreshAll() wb.Save() wb.Close() excel.Quit() Runtime.InteropServices.Marshal.ReleaseComObject(excel) Catch ex As Exception Throw New Exception End Try End Sub ThanksMattF

Matt, To be clear you are using remote desktop or similar to actually logon directly to the server desktop console, on which you are running BIDS. On that server you have installed both Excel 2007, and the Office 2007 PIA. Is that correct?http://www.sqlis.com | http://www.konesans.com

Hi Darren, There are two scenarios: 1. The script task works on my client PC which has BIDS, Excel 2007 and the Office 2007 PIA. 2. Deployed to 64-bit server and get the error at runtime. RD to the server, open the package in BIDS and get the error running in design mode. Server has PIA installed, but not Excel 2007 (would prefer not to install this unless there is no other option). Have also re-added references to the Microsoft.Office.Interop.Excel.dll on the server but continue to get the error. (Reference path is "C:\Program Files(x86)\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll") Thanks for your help with this. MattF

Matt, At a basic level, a Primary Interop Assembly is a .NET wrapper over a COM object. In this case it is a wrapper over MS Excel's COM automation objects. You need to install Excel to get the COM objects. The PIA is the (official) bridge between .NET and COM for those types, it is not the implementation.http://www.sqlis.com | http://www.konesans.com

Thanks that was it - I had assumed the VSTO install was like an SDK (eg. reference path is "C:\Program Files(x86)\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll).MattF

Thanks that was it - I had assumed the VSTO install was like an SDK. Ths package now runs Ok on the server in Integration Services - but when I schedule it as a SQL Agent job it fails with the same error. I have set it to run with the 32-bit runtime, also tried the suggestion in this similar thread http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b45d566f-e93a-41ca-b1c7-70921a287fea without success. Error details: Executed as user: domain\admin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:55:40 AM Error: 2010-11-17 11:55:41.07 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-11-17 11:55:43.47 Code: 0x00000001 Source: Refresh Excel (Sharepoint) datasource Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: Exception of type 'System.Exception' was thrown... Any further suggestions much appreciated. MattF

Thanks that was it - I had assumed the VSTO install was like an SDK. Ths package now runs Ok on the server in Integration Services - but when I schedule it as a SQL Agent job it fails with the same error. I have set it to run with the 32-bit runtime, also tried the suggestion in this similar thread http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b45d566f-e93a-41ca-b1c7-70921a287fea without success. Error details: Executed as user: domain\admin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:55:40 AM Error: 2010-11-17 11:55:41.07 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-11-17 11:55:43.47 Code: 0x00000001 Source: Refresh Excel (Sharepoint) datasource Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: Exception of type 'System.Exception' was thrown... Also created this package on the server as the same user which runs the SQL agent service - the package runs ok in BIDS and SSIS, but the job still fails. This would imply that credentials are not the issue. Any further suggestions much appreciated. MattF

Another twist - running the package using dtexec and scheduling with the Task Scheduler works. This is using the same credentials as the SQL Agent. eg. "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtExec.exe" /F "C:\MyPackage.dtsx" - running this same script in SQL agent as a cmdexec fails.MattF

Matt please read http://support.microsoft.com/kb/918760 Th error "Failed to decrypt protected XML node " relates to your PackageProtectionLvel, which I'd suggest setting to DontSaveSensitive and using configurations to supply any passwords requried. "running this same script in SQL agent as a cmdexec fails." can you give the error.http://www.sqlis.com | http://www.konesans.com

Ok - the package already uses a config file - setting the PackageProtectionLevel to "DontSaveSensitive" removed the "Failed to decrypt protected XML node " line from the error output - but still got the original "... Exception has been thrown by the target of an invocation" error. So I added logging to the package and it gave extra info re unable to access the file. All of this occurred for the same Admin user, running in BIDS, SSIS and the same user runs the SQL Agent service. Looks as though SQL Agent has an issue with network permissions that SSIS does not. ThanksMattF

Matt, SQL Agent doesn't have an issue with network permissions, it will be down to the security context used to execute the package. Please double check the service account, and that no proxy is specified.http://www.sqlis.com | http://www.konesans.com

Darren, without making any changes to the service account (there is no proxy) I got the agent job to run against a folder with no permissions setup. It fails even when the folder permissions for the service account are set to full control. Since this has gone beyond SSIS and appears to be more relevant to SQL Agent, I will post an issue in another forum. Thanks again for your assistance with this. MattF