SSIS System.Reflection.TargetInvocationException

I've recently built an SSIS package (2008) which consumes an Excel 2007 spreadsheet (.xlsx extension). The contents of the spreadsheet are dumped into a recordset object which is then passed to a Script Task Editor which iterates through each row, formats the data in the appropriate manner and then writes it out to a csv file.

The CSV output goes to the same network location as the Excel file was found in. On my local development environment everything works exactly as it should, but when the package is deployed to the live SQL server and executed from there, we get the following error:

Error: 2018-02-16 12:42:41.60 Code: 0x00000001 Source: Export Recordset To CSV Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.

---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: "component "Recordset Destination" (338)" wrote 564 rows.

---> System.Runtime.InteropServices.COMException (0xC0010014): "component "Recordset Destination" (338)" wrote 564 rows.

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariable100.get_Value()

at Microsoft.SqlServer.Dts.Runtime.Variable.get_Value()

--- End of inner exception stack trace ---

at Microsoft.SqlServer.Dts.Runtime.Variable.get_Value()

at ST_5a1e9be825584201adc4e2b6248e429e.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()

End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 12:42:39 Finished: 12:42:41 Elapsed: 1.64 seconds.

The package execution failed. The step failed.

Initially we thought that the issue was because the package was trying to run the 64-bit version of Excel, but we've ruled that out as a cause.

I'm at a complete loss to understand why the package is working without issue on my local machine, but is failing on the remote server, especially since the code that I'm using for this is almost identical to the code in a separate package which works without issue.

I would ensure that the CSV you are dumping to has the correct permissions for the package to write to. Also your environment is patched up to date.

I did find a post here where someone mentions having a similar problem to yours. He said he got around it by removing and rebuilding the recordset object - I'd try that next if the above doesn't work.