Hi, I'm using SQL Server 2008 R2 SP1 on Windows 7 OS. I'm running an SSIS package on my local machine and the package is located in C drive. I created a SSIS package with 32 bit version of Excel as source and destinations in different DFTs. For this to run properly, I changed the Run64BitRuntime to False. The package is running fine. But when I create a job to run this package it failed. I created a Credential, Proxy and windows user login with access to the folders where excel files are present. I changed the job owner to the new login which I created and job step RunAs to the proxy account. This time I got a different error as below: Executed as user: USHYDDJANDHYAL7\SQLAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:51:23 AM Info: 2012-07-22 01:51:24.32 Code: 0x4004300A Source: DFT_Excel To SQL -- Dates SSIS.Pipeline Description: Validation phase is beginning. End Info Progress: 2012-07-22 01:51:24.32 Source: DFT_Excel To SQL -- Dates Validating: 0% complete End Progress Error: 2012-07-22 01:51:24.40 Code: 0xC0202009 Source: SSIS_Pkg_RU Connection manager "Excel Connection Manager 1" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error Error: 2012-07-22 01:51:24.40 Code: 0xC020801C Source: DFT_Excel To SQL -- Dates Excel Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-07-22 01:51:24.40 Code: 0xC0047017 Source: DFT_Excel To SQL -- Dates SSIS.Pipeline Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2012-07-22 01:51:24.40 Source: DFT_Excel To SQL -- Dates Validating: 50% complete End Progress Error: 2012-07-22 01:51:24.40 Code: 0xC004700C Source: DFT_Excel To SQL -- Dates SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-07-22 01:51:24.40 Code: 0xC0024107 Source: DFT_Excel To SQL -- Dates Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:51:23 AM Finished: 1:51:24 AM Elapsed: 1.31 seconds. Process Exit Code 1. The step failed. When I run the package from the SSMS --> Stored Packages --> MSDB, this is executing perfectly and when I run it from CMD, it is running successfully. But when I run the job, it is failing. I gave the required permissions to the login like Sysadmin server role, and in user mapping, for msdb database, checked the SQL Agent related db roles and db_owner as well. In the job step, I selected CMD and gave the fully qualified path of the 32 bit of DTExec.exe and the remaining parameters like /SQL, Server name, package name etc. I even changed the Protection level to DoNotSaveSensitive but still no luck. I'm not understanding why the package is failing through job but not through BIDS, CMD, SSMS. Could anyone please help me out with this issue? Regards, djandy

in job step properties; Go to Execution Options tab; check the Use 32 bit runtime another note: does the proxy account have enough permission to the folder which excel file exists?http://www.rad.pasfu.com

Hi, In the job step, I selected CMD and gave the fully qualified path of the 32 bit of DTExec.exe and the remaining parameters like /SQL, Server name, package name etc. As mentioned in my initial post (the above sentence), I used the fully qualified path of 32 bit DTexec.exe and gave the package name and other parameters. But it did not work. As mentioned in your replies, I also tried selecting SSIS package and 32 bit in execution tab. Still no luck. I think the proxy account has enough permissions. I'm saying this because, I opened BIDS and SSMS with right click --> RunAs --> DifferentUser --> Gave the credentials of proxy account. From there I'm executing the package which is running fine. I also tried logging into the system with the windows user used for the login(proxy account) and executing the package through BIDS and SSMS and it was fine. But the job alone fails. I tried creating a simple package which will insert data into a flat file where the flat file is stored in the same folder as the excel file which I mentioned in my post. This is working fine. It means the proxy account has access to that folder and files. I'm able to insert data into the text file where the excel files are located. I've been trying many methods for hours but nit finding the solution. Regards, djandy

Hi, I created a sample package with excel as source and flat file as destination and executed the job. It gave the same error as I mentioned in my previous posts. I changed the RunAs step from the proxy account to SQLServer Agent Service account and it was successful. But now I have another problem. There is also a script task in the same package and now it is giving the error. The script task has some VB .NET code which will open th excel and do some formatting and save it and close it. Now this task is giving me the problem when run through job. Below is the error in job history: Executed as user: US\USHYDDJANDHYAL7$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:31:17 PM Error: 2012-07-22 12:31:18.97 Code: 0x00000001 Source: ST_Clean Excel Template Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.UnauthorizedAccessException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005. at ST_75c5c87f1aa14fe48803f5e30219c6b6.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:31:17 PM Finished: 12:31:18 PM Elapsed: 1.482 seconds. The package execution failed. The step failed. Any help on this? Regards, Djandy

Hi All, Any thoughts on this issue? Could anyone help me on this issue? Regards. djandy

Ensure all the needed supporting objects for excel and anything else are available on the server. Ted Krueger Blog on lessthandot.com @onpnt on twitter Troubleshooting SQL Server: A Guide for the Accidental DBA Please click the Mark as Answer button if a post solves your problem!

Hi, All the objects are available. That is the reason I'm able to run SSIS package manually from BIDS, SSMS and CMD but not through job. Even I logged into the system using the proxy account login and ran it in BIDS, CMD and SSMS. It was fine. Only the job is failing. Regards, djandy

Run through this check list and see if it uncovers the problem. http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/ssis-runs-in-bids-but-not-with-sql-agentTed Krueger Blog on lessthandot.com @onpnt on twitter Troubleshooting SQL Server: A Guide for the Accidental DBA Please click the Mark as Answer button if a post solves your problem!

Hi , Can you please suggest solution for this error , The package is running fine. But when I create a job to run this package it failed and error is 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 'D:\forbes\forbes.xlsx'. 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_bed2fc9e056c411a8aa2774bc1e923bd.csproj.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()

Dear d jandy, I am facing the similar problem.pl Explain me about the proxy login change and let me know.