Hi all, I've created a SSIS package which firstly runs an execute sql task, this retrieves a list of excel file paths and pokes them into a variable of type object called 'Files'. e.g. DocumentName \\ptdckbox01\d$\bi\Outlets\******\Report Summary – Week 22 \\ptdckbox01\d$\bi\Outlets\******\Report Summary – Week 22 \\ptdckbox01\d$\bi\Outlets\******\Report Summary – Week 22 I then have a for each loop container to read in 'Files' varible and transform this to a varible of type string called 'FileName'. Within the for each loop container I run a script task to do some excel formatting and use the FileName varible to point the script at the correct file. In theory this all works great but when I execute the SSIS package it falls over with the following error... ------------------------------------------------------------------------------------------------------------- SSIS package "GOSH_EXCEL_AUTO_EDIT.dtsx" starting. Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: Cannot create ActiveX component. at Microsoft.VisualBasic.Interaction.GetObject(String PathName, String Class) at ST_7c4c200dfbf342a5b81bca0523921f00.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() Task failed: Script Task Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at GOSH_EXCEL_AUTO_EDIT: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "GOSH_EXCEL_AUTO_EDIT.dtsx" finished: Failure. -------------------------------------------------------------------------------------------------------------- Below is the script code.... ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic 2008. ' The ScriptMain is the entry point class of the script. Option Explicit On Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Imports Microsoft.Office.Interop.Excel <System.AddIn.AddIn("ScriptMain" , Version:="1.0" , Publisher:="" , Description:="" )> _ <System.CLSCompliantAttribute(False )> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum 'Define the required variable Dim Excel As Microsoft.Office.Interop.Excel.Application ' This is the excel program Dim ExcelWBk As Microsoft.Office.Interop.Excel.Workbook ' This is the work book Dim ExcelWS As Microsoft.Office.Interop.Excel.Worksheet ' This is the sheet Public Sub Main() Dim FileName As String = Dts.Variables("User::FileName" ).Value.ToString 'Dts.Variables("User::FileName").Value Excel = GetObject(, "Microsoft.Office.Interop.Excel.Application" ) ' Create Excel Object ExcelWBk = Excel.Workbooks.Open(FileName) ExcelWS = ExcelWBk.Worksheets(1) Excel.Visible = False Call Formatting() Excel.Visible = True ExcelWBk.Close() Excel.Quit() ExcelWBk = Nothing Excel = Nothing End Sub Sub Formatting() With ExcelWS With .Range("1:1" ) .Orientation = Excel.Landscape .PaperSize = Excel.PaperA4 .Order = Excel.DownThenOver .RightFooter = "Page &P of &N" .Zoom = False .PrintArea = "$A:$AL" .FitToPagesWide = 1 .FitToPagesTall = 1 .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .MergeCells = False With .Range("9:41" ).RowHeight = 24 End With End With With .Range("A:A" ).ColumnWidth = 10.0# End With End With End Sub End Class Can anyone see anything obvious that is causing this package to fall over? Any help would be greatly appreciated. Andy

Does the account running the package have access to the filepath: \\ptdckbox01\d$\bi\Outlets\******\ I've had odd issues with this sort of thing before on a shared drive

Hi, Yes the account running this has full Admin privilages

Error seems to indicate that the line Excel = GetObject(, "Microsoft.Office.Interop.Excel.Application" ) ' Create Excel Object may be at fault as the stacktrace states: Cannot create ActiveX component. at Microsoft.VisualBasic.Interaction.GetObject(String PathName, String Class) does this need both variables in the method call? There's a similar discussion here: http://www.eggheadcafe.com/software/aspnet/30873538/error-with-microsoftvisualbasicinteractiongetobject.aspx

Also, is Excel installed on the machine that is running this SSIS package?

Ok, i was hopeful that might have been a fix, with both in the method i get a different error.... Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: File name or class name not found during Automation operation. at Microsoft.VisualBasic.Interaction.GetObject(String PathName, String Class)

sounds like Excel isn't installed if it can't load that particular class

excel is definately installed, im out of ideas

anyone else have any thoughts?

anyone else have any thoughts? Is this 64bit machine? If that is the case, you have to force your package to execute in 32bit mode. The Excel application automation is only available in 32bit.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/