SSIS Package Works in BIDS Does Not Work SQL Server Agent HELP!!!!!

[bmann]

I have a SSIS Package that works in BIDS and it works by double clicking the SSIS package. But when I schedule Job using SSIs package i get an error. Please let me know if you have any questions. thanks. Error is below:

Message
Executed as user: SCONET\svc_BIGCCPRD84_SSA. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:33:24 PM Error: 2012-04-19 14:33:26.39 Code: 0xC002F304 Source: Email error Send Mail Task Description: An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.1.1.243:25". End Error Error: 2012-04-19 14:33:26.39 Code: 0x00000001 Source: Unlock workbook Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file '\\SCONET\Data\SCO\Group\LGCR Files For Upload\2010-Group 2-12264807800-4112012.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. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at ST_22080cd5ccf5484fa05df02546b8d5de.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: 2:33:24 PM Finished: 2:33:26 PM Elapsed: 1.578 seconds. The package execution failed. The step failed.

 

[TomKane]

Hi,

Taking a bit of a stab at this but it appears to me that you have a SSIS package with a send mail task that is attempting to send an excel file as an attachment but it can't find it.

This can happen when the SQL Server Agent account doesn't have permissions to access the file you're trying to send as an attachment. You can either give the SQL Agent account access to the file location or you could create a proxy account for that job.

If the SSIS package is stored on the file system then there is potentially a sticking plaster solution that I have seen and it could potentially help if you don't want to rock the boat with your DBAs and changing permissions or adding accounts, etc and that is to run the SSIS package on Windows Scheduler. There are a couple of reasons why this may not work in your environment but I have see it used and it can work pretty well.

Good luck!

 

[bmann]

Thanks TomKane.

But still not working My server is Windows 2008 Server R2 Data Center edition, Excel 2007 and SQL Server 2008 R2 Enterprise edition. I have a SSIS package that runs successfully in business intelligence development studio but fails to run under sql server agent as a job. The SSIS package processes an excel file from a file location.

I have tried the solutions above. Still does not work

Do you know what else could be the problem. Here is my error below:

Executed as user: SCONET\svc_BIGCCPRD84_SSA. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:31:05 PM Error: 2012-04-18 15:31:10.07 Code: 0xC002F304 Source: Email error Send Mail Task Description: An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.1.1.243:25". End Error Error: 2012-04-18 15:31:10.07 Code: 0x00000001 Source: Unlock workbook Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file '\\SCONET\Data\SCO\Group\LGCR Files For Upload\2010-Group 2-12264807800-4112012.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. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at ST_22080cd5ccf5484fa05df02546b8d5de.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: 3:31:05 PM Finished: 3:31:10 PM Elapsed: 4.672 seconds. The package execution failed. The step failed.

 

[TomKane]

Are you able to verify that \\SCONET\Data\SCO\Group\LGCR Files For Upload\2010-Group 2-12264807800-4112012.xls - is accessible from the server and that the SQL Agent account has the appropriate access?

 

[Olaf Doschke]

And the XLS file is not open, while the Agent runs the package?

Besides there is another error:
"Failure sending mail. System.Net.WebException: Unable to connect to the remote server"

I assume this means the mail server, for that matter. SQL Agent can't log into smtp or imap mail server.

Bye, Olaf.

 

[bmann]

Hello TomKane,

Yes \\SCONET\Data\SCO\Group\LGCR Files For Upload\ is accessible by sql server agent. I don't have access to those permissions but my network unit says the service account that sql server agent uses has access. Are their any other solutions to this problem? I appreciate all your help. I have this same setup on another server and it works fine.

 

[bmann]

Hello TomKane,

I made some tweaks and got this new error:


Executed as user: SCONET\svc_BIGCCDEV84_SSA. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:11:38 AM Error: 2012-04-21 08:11:41.68 Code: 0xC002F304 Source: Email error Send Mail Task Description: An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.1.1.243:25". End Error Error: 2012-04-21 08:11:41.68 Code: 0x00000001 Source: Unlock workbook Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x8001010A): Creating an instance of the COM component with CLSID {00024500-0000-0000-C000-000000000046} from the IClassFactory failed due to the following error: 8001010a. at ST_22080cd5ccf5484fa05df02546b8d5de.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: 8:11:38 AM Finished: 8:11:41 AM Elapsed: 3.25 seconds. The package execution failed. The step failed.

 

[TomKane]

Olaf referred to the mail server and he had a good point. Does the server have access to the mail server set up in the send mail task? I know that in my environment the test server can't access the product mail server and visa versa.

 

[TomKane]

I meant to say production mail server not product mail server