I have an SSIS package that reads data using a custom query and stores it into a target excel source. I am able to run the SSIS package successfully on a server where no excel is installed by doing a right click and selecting the Run Package option. When I add the same SSIS package to Integration services and run the package, it still executes successfully. But, when I schedule the package using SQL Server agent job, it fails with the below error:
"Executed as user: Username. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Argument "Excel" for option "connection" is not valid. The command line parameters are invalid. The step failed."
Specification of SQL Server: SQL SERVER 2008 R2 Executing as SQL Server Agent Job.
Tried the following: 1) Checked the 'Use 32 bit' check box under Execution options tab in Step menu and unchecked all the check boxes in Datasources tab. Got the below error:
"Executed as user: Username. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:44:03 PM Error: 2015-06-08 21:44:04.21 Code: 0xC002F304 Source: File System Task File System Task Description: An error occurred with the following error message: "The device is not ready. ". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:44:03 PM Finished: 9:44:04 PM Elapsed: 1.219 seconds. The package execution failed. The step failed."
2) Created a proxy account that had sysadmin, public, serveradmin roles. Modelled the package to run as proxy account. Got the below error:
"Executed as user: Username. The process could not be created for step 1 of job (reason: 1314). The step failed." The command line looks almost similar to the below: /DTS "\File System\PackageName" /SERVER SERVERNAME /CONNECTION "InputFileConn.xlsx";"F:\Folder\InputFile.xlsx" /CONNECTION "OutputFileConn.xlsx 1";"F:\Folder\Output\OutputFile.xlsx" /CONNECTION "Excel Connection Manager 1";"\"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Folder\Output\OutputFile.xlsx;Extended Properties=""EXCEL 12.0;HDR=YES"";\"" /CONNECTION "SERVERINSTANCE.DBName";"\"Data Source=ServerInstance;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{D4E09BF5-DF25-3DE7-BCAE-B667C38F85B7}SERVERNAME\SQLSERVER.DatabaseName;\"" /X86 /CHECKPOINTING OFF /REPORTING E
Is it necessary to install excel in the server to make the SQL Server agent job work?
Based on the discussion above, the job was referring to F drive but F is a different drive to expected when running on the server.