0

I have set up a package on SSIS 2008 with a vb script that runs an excel macro. It works on Business Intelligence Development Studio (BIDS) 2008 but still I get the following error: 

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object. 
at ST_e916156b0e6449b58e21905bd635ecf0.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()

Also when I run the package with SQL agent, the job is successful but the macro is not executed and I get the following error: 

Executed as user: 'myusername'. 
Code: 0xFFFFFFFF 
Source: Run macro script excel macro Description: Microsoft Excel cannot access the file 'book1.XLS'. 
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. 
End Error 
DTExec: The package execution returned DTSER_SUCCESS (0). 
Started: 11:18:15 Finished: 11:18:19 
Elapsed: 3.775 seconds. 
The package executed successfully. The step succeeded.

However, I have full access to the local server and network and I have tried to run on both 32 bit and 64 bit but no success.

How I can resolve this issue?

Virginie All
  • 1
  • 1
  • 1
    This looks like an exception in a script task. Take a look at the code of your task. See http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-in-net for ideas while you look at the script task. – John Saunders Feb 21 '13 at 16:28
  • I have had a look but I can't see where the exception comes from in the script.. – Virginie All Feb 22 '13 at 10:53
  • Public Sub Main() Dim Macro_name As String Dim File_Name As String Dim File_Name2 As String Dim ExcelObject As New Microsoft.Office.Interop.Excel.Application ExcelObject.Visible = True ExcelObject.DisplayAlerts = False ExcelObject.UserControl = False Try Dim oBook As Microsoft.Office.Interop.Excel.Workbook 'wb Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks Dim pbCancel As Boolean = False Macro_name = "Macro001" File_Name = "c\book1.xls" – Virginie All Feb 22 '13 at 10:57
  • File_Name2 = "c\book2.xls" ExcelObject = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application) ExcelObject.Visible = True ExcelObject.UserControl = False ExcelObject.DisplayAlerts = False oBooks = ExcelObject.Workbooks oBook = CType(oBooks.Open(File_Name2), Microsoft.Office.Interop.Excel.WorkbookClass) oBook = CType(oBooks.Open(File_Name), Microsoft.Office.Interop.Excel.WorkbookClass) ExcelObject.Run(Macro_name) ExcelObject.DisplayAlerts = True – Virginie All Feb 22 '13 at 10:58
  • ExcelObject = Nothing Dim fireAgain As Boolean = True Catch ex As Exception Dts.Events.FireError(-1, "script excel macro", ex.Message, String.Empty, 0) Dts.TaskResult = ScriptResults.Failure End Try ExcelObject.Application.Quit() Dts.TaskResult = ScriptResults.Success End Sub End Class – Virginie All Feb 22 '13 at 10:59
  • PUT THE CODE IN YOUR QUESTION, not in a comment! – John Saunders Feb 22 '13 at 14:32

1 Answers1

0

Things you could try:

In my personal experience, Excel is always tricky when working with SSIS. As you could see from the error message, verify that you can rule out the following information with respect to the issue.

  • Check the user account under which the SQL Server Agent service is running. You can do this by navigating to Windows Start \ Control Panel \ Administrative Tools \ Services. Look for a service named SQL Server Agent (<your instance name>)

  • Make sure that the account does have access to the folder where Excel file is stored.

  • Verify that you do not have the Excel file open.

  • On the SQL Server Agent job, click the step that executes the package. If you are running the package under the type SQL Server Integration Services Package, make sure that you have the box User 32 bit runtime checked on the Execution options tab.

  • You could also manually double-click the package file (.dtsx). This will bring the DTEXEC utility which executes the packages. If you are using configuration file, attach the configuration file on Configurations tab. Run the package. If it runs in DTEXEC utility under your credentials but not under SQL Server Agent, I usually found that to be related permissions issue.

Community
  • 1
  • 1